Unable to copy data between tables using the syntax: `INSERT INTO test2 (SELECT * FROM test)` about crate HOT 5 CLOSED

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.