CODEWITHSUNDEEP
postgresql
Rong Nguyen
Rong Nguyen

Reputation: 4189

Postgres: Error when using COPY from a CSV with timestamptz type

I am using Postgres 9.5.3(On Ubuntu 16.04) and I have a table with some timestamptz fields 

...
datetime_received timestamptz NULL,
datetime_manufactured timestamptz NULL,
...

I used the following SQL command to generate CSV file:

COPY (select * from tmp_table limit 100000) TO '/tmp/aa.csv' DELIMITER ';' CSV HEADER;

and used:

COPY tmp_table FROM '/tmp/aa.csv' DELIMITER ';' CSV ENCODING 'UTF-8';

to import into the table.

The example of rows in the CSV file:

CM0030;;INV_AVAILABLE;2016-07-30 14:50:42.141+07;;2016-08-06 00:00:000+07;FAHCM00001;;123;;;;;1.000000;1.000000;;;;;;;;80000.000000;;;2016-07-30 14:59:08.959+07;2016-07-30 14:59:08.959+07;2016-07-30 14:59:08.959+07;2016-07-30 14:59:08.959+07;

But I encounter the following error when running the second command:

ERROR:  invalid input syntax for type timestamp with time zone: "datetime_received"
CONTEXT:  COPY inventory_item, line 1, column datetime_received: "datetime_received"

My database's timezone is:

 show timezone;
 TimeZone  
-----------
 localtime(GMT+7)
(1 row)

Is there any missing step or wrong configuration?

Any suggestions are appreciated!

Upvotes: 2

Views: 2025

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21326

The error you're seeing means that Postgres is trying (and failing) to convert the string 'datetime_received' to a timestamp value.

This is happening because COPY is trying to insert the header row into your table. You need to include a HEADER clause on the COPY FROM command, just like you did for the COPY TO.

More generally, when using COPY to move data around, you should make sure that the TO and FROM commands are using exactly the same options. Specifying ENCODING for one command and not the other can lead to errors, or silently corrupt data, if your client encoding is not UTF8.

Upvotes: 1

Related Questions