I'm trying to import data from a .csv file into a postgresql 9.2 database using the psql \COPY
command (not the SQL COPY).
The input .csv file contains a column with a timestamp in the dd.mm.yyyy hh.mm.ss format.
I've set the database datestyle to DMY using.
set datestyle 'ISO,DMY'
Unfortunately, when I run the \COPY
command:
\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: "16.11.2012 07:10:06"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: COPY trace, line 2, column timestamp_mes: "16.11.2012 07:10:06"
What is wrong with the datestyle?
Have you tried setting the datestyle
setting of the server?
SET datestyle = 'ISO,DMY';
You are using the psql meta-command \copy
, which means the input file is local to the client. But it's still the server who has to coerce the input to matching data-types.
More generally, unlike the psql meta-command \copy
which invokes COPY
on the server and is closely related to it .. I quote the manual concerning \set
:
Note: This command is unrelated to the SQL command SET.