Comments (5)
I just saw that you PARTITIONED BY ("timestamp")
which isn't a generated field and would lead to CrateDB initialising 50 partitions with a single query.
I quickly played around with older versions and could validate that form 4.6 - 5.2 the following query fails:
CREATE TABLE IF NOT EXISTS "my_schema"."test" (
"test_int" INTEGER,
"test_string" TEXT,
"timestamp" TIMESTAMP WITH TIME ZONE,
"edate" TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('week', "timestamp")
)
CLUSTERED INTO 4 SHARDS
PARTITIONED BY ("timestamp");
INSERT INTO my_schema.test (test_int,test_string,"timestamp") SELECT 1,'a',x FROM generate_series(now(),now()+'50 days'::INTERVAL,'1 day'::INTERVAL) g(x);
Partitions are created, but not records are inserted.
This is fixed with CrateDB 5.3.4 and newer. I think this is related to:
Fixed an issue which prevented INSERT INTO ... SELECT ... from inserting any records if the target table had a partitioned column of a non-string type, used in any expressions of GENERATED or CHECK definitions.
Changing your table definition to
CREATE TABLE IF NOT EXISTS "my_schema"."test" (
"test_int" INTEGER,
"test_string" TEXT,
"timestamp" TIMESTAMP WITH TIME ZONE,
"edate" TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('week', "timestamp")
)
CLUSTERED INTO 4 SHARDS
PARTITIONED BY ("edate");
and partitioning by "edate"
also works with older versions as this avoids a "partitioned column of a non-string type ("timestamp"
), used in any expressions of GENERATED ("edate"
)"
Considering this issue as identified and fixed I will close this issue.
from crate.
Hi,
4.6 is a bit dated, a lot of bugs have been fixed since, but I would suggest trying without the generated column:
INSERT INTO my_schema.table2 ("test_int","test_string","timestamp")
SELECT "test_int","test_string","timestamp"
FROM my_schema.table1
from crate.
Hey! I had already tried your suggestion and it also doesn't work.
from crate.
How did you get data into "my_schema"."test"
?
With old CrateDB versions CrateDB didn't fully validate COPY FROM
imports in some cases.
However without any example data to replicate this, I rather tend to close this, as there is nothing to do here.
There is an issue tracking feedback with failing INSERT INTO SELECT
already #12218
from crate.
The data inserted into the test table was generated by a script, just for testing purposes. This is it:
$startTimestamp = Get-Date "2024-03-31T13:59:45"
$timestamps = @()
for ($i = 0; $i -lt 50; $i++) {
$timestamp = $startTimestamp.AddDays(-$i)
$timestamps += $timestamp.ToString("yyyy-MM-ddTHH:mm:ss")
}
$query = "INSERT INTO my_schema.test (timestamp) VALUES "
foreach ($timestamp in $timestamps) {
$query += "('$timestamp'),"
}
# Remove the trailing comma
$query = $query.TrimEnd(",")
$query
Which results in a query similar to: INSERT INTO my_schema.test (timestamp) VALUES ('2024-03-31T13:59:45'),('2024-03-30T13:59:45'), ...
which I ran directly in the web console
from crate.
Related Issues (20)
- Unexpected result when using escape character in `LIKE` HOT 2
- Cast to geo_shape not working on generated column HOT 2
- NPE on `MIN` aggregation on a mixed-version cluster 5.8.0/5.7.3 HOT 1
- Alternating exceptions/results with geo_shape UDF and MATCH USING WITHIN
- Enable logging in ECS JSON format
- Allow users with `AL` privileges to manage session settings defaults for other users HOT 1
- `COUNT()` from a nested view should use `COUNT` operator instead of `COLLECT`
- StaticInformationSchemaQueryTest.testIsNotNull test failure HOT 2
- Success `statusCode` for failed bulk inserts with HTTP endpoint HOT 1
- Extra flags to provide further details on failed records on HTTP endpoint bulk insertion HOT 1
- Improve `ARRAY_LENGTH` performance for arrays within object arrays HOT 1
- Improve performance in HyperLogLogDistinctAggregation
- Invalid results when filtering with `ARRAY_LENGTH` on an array in array of objects HOT 2
- IndexOutOfBoundsException on query with ORDER BY in 3 nodes cluster HOT 3
- ShardInfo in NodeInfo MXBean is missing the schema name leading to duplicates HOT 4
- Table with dynamic column policy is silently failing first insert if it includes an empty array HOT 5
- Unexpected result when using empty `OBJECT` literal HOT 2
- Behaviour of numeric scale and casting inconsistent / unspecified HOT 2
- PostgreSQL wire protocol: type OID for text responses in prepared statements is invalid HOT 4
- PostgreSQL compatibility: SQL statement containing only comment not accepted HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from crate.