Error on using copy from csv file with a timestamp


I want to insert Data from a CSV file into a table. The table has two colums of type timestamp with timezone and real. The CSV file locks like the following:


And the copy from statement like this:

COPY mtcontroller.test_import

The copy from statement excutes with succes. But when I want to qurey the data with this:

SELECT time_index, battery_voltage
FROM "mtcontroller"."test_import"
LIMIT 100;

I get the following error:
SQLParseException[Cannot convert input string: “2023-11-28T15:56:00.000Z” to biginteger]

Has anyone an idea what I am doing wrong?

1 Like

Which version of CrateDB are you using?
This should work with newer versions

We are using Version 4.6.6.

Typecasting and validation for COPY FROM was added with 4.8.0.
In earlier versions it was merely a raw copy from the source file to the target table.

If you don’t want to update to a newer version, you might want to import all data as TEXT and then transform in a 2nd step using a INSERT INTO target_table SELECT time_index::TIMESTAMP, battery_voltage FROM temp_table

1 Like