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

Question

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!

Just a suspicion: change 2016-08-06 00:00:00+07 to 2016-08-06 00:00:00.000+07 on that row and run it again please. — Jorge Campos
– Jorge Campos, Commented Aug 2, 2016 at 2:52
Testing here with a simplified table but direct copy of your values, it's loading fine, the date format is ok. Can you provide your actual table structure (CREATE TABLE statement) please? — alzee
– alzee, Commented Aug 2, 2016 at 3:20
It's trying (and failing) to interpret the string 'datetime_received' as a timestamp. You forgot to tell COPY FROM to ignore the header. — Nick Barnes
– Nick Barnes, Commented Aug 2, 2016 at 3:25

Nick Barnes · Accepted Answer · 2016-08-02 10:09:08Z

1

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.

answered Aug 2, 2016 at 10:09

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related