Importing CSV file into cockroachdb , error in date time format

1
votes

I am importing a CSV file into a table in cockroachdb with the following command:

\| IFS=","; while read a1 a2; do echo "INSERT INTO sales VALUES ($a1, $a2);"; done < sales.csv;

My csv file has only two rows:

ID, time
14,2013-02-17 05:39:36.710332000

Data types of the columns:

first column: NUMBER(10, 0)
second column: TIMESTAMP

I get the following error:

syntax error at or near "5"

INSERT INTO sales VALUES (14, 2013-02-17 05:39:36.710332000
                                         ^

Based on the CockroachDB documentation, I think I am using the correct TIMESTAMP format (YYYY-MM-DD HH24:MI:SSXFF).

Any suggestions?

I have also tried to change the data in second column without milliseconds, like

2013-02-17 05:39:36

but I get the same error.

1
csvtimestampsql-insertcockroachdb

1 Answers

2
votes

You need to quote your timestamp string:

jordan@:26257/test> INSERT INTO derp VALUES (1, 2013-02-17 05:39:36.710332000);
invalid syntax: statement ignored: syntax error at or near "5"
jordan@:26257/test> INSERT INTO sales VALUES(1, '2013-02-17 05:39:36.710332000')
INSERT 1

You can modify your script to do that too, like so:

\| IFS=","; while read a1 a2; do echo "INSERT INTO sales VALUES ($a1, '$a2');"; done < sales.csv;