Error on using copy from csv file with a timestamp

CrateDB SQL
1

Hello,

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:

time_index,battery_voltage
2023-11-28T15:56:00.000Z,50.5

And the copy from statement like this:

COPY mtcontroller.test_import
FROM 'https://raw.githubusercontent.com/.../main/test_new.csv'
RETURN SUMMARY;

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
2

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

3

We are using Version 4.6.6.

4

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