Import .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \ copy

Question

I am trying to import data from a CSV file into the postgresql 9.2 database using the psql \COPY (rather than COPY SQL).

The input .csv file contains a timestamped column in the format dd.mm.yyyy hh.mm.ss.

I installed the datestyle database in DMY using.

 set datestyle 'ISO,DMY' 

Unfortunately, when I run the \COPY :

 \COPY gc_test.trace(numpoint,easting,northing,altitude,numsats,pdop,timestamp_mes,duration,ttype,h_error,v_error) FROM 'C:\data.csv' WITH DELIMITER ';' CSV HEADER ENCODING 'ISO 8859-1' 

I get this error:

ERROR: date / time field value out of range: "11.16.2012 07:10:06"

TIP. Perhaps you need a different "datestyle" setting.

CONTEXT: trace COPY, line 2, column timestamp_mes: "11.16.2012 07:10:06"

What is wrong with datestyle?

Answer 1

Have you tried setting the datestyle server parameter?

 SET datestyle = 'ISO,DMY'; 

You are using the psql \copy meta command, which means the input file is local to the client. But it is still a server that should force entry to the appropriate data types.

In general, unlike the psql \copy meta-command, which invokes COPY on the server and is closely related to it. I quote the manual regarding \set :

Note. This command is not related to the SQL SET command.

Answer 2

I found it difficult to apply 'SET datestyle' in the same session when using the psql command. Changing the datestyle on the entire database / server (import only) can also cause side effects for other users or existing applications. Therefore, I usually modify the file before downloading:

 #!/bin/bash # # change from dd.mm.yyyy to yyyy-mm-dd inside the file # note: regex searches for date columns separated by semicolon (;) sed -i 's/;\([0-9][0-9]\)\.\([0-9][0-9]\)\.\([0-9][0-9][0-9][0-9]\);/;\3-\2-\1;/g' myfile # then import file with date column psql <connect_string> -c "\COPY mytable FROM 'myfile' ...." 

Answer 3

The date style you seem to be using is German. PostgreSQL supports this date style. Try using this:

 SET datestyle TO German;