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

Question

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?

Solution 1

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.

Solution 2

I found it difficult to apply 'SET datestyle' within the same session when applying the psql command. Altering the datestyle on the whole database/server (just for the import) also might cause side effects on other users or existing applications. So i usually modify the file itself before loading:

#!/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' ...."

Comments

[jatobat]

Thanks for your help. I modified the configuration manually to datestyle = 'iso, dmy' in the postgresql.conf file and the import works fine now. Not sure if that's what you are suggesting. I am however looking for a way to set the server datestyle with a psql command.

[Erwin Brandstetter]

Well, modifying postgresql.conf is one radical way to do it. You can also just issue the SQL command as stated in your session before you running \copy. Then the setting is only changed for that session.

[jatobat]

I've tried just running the set datestyle = 'ISO, DMY'; SQL command in the editor (before modifying the postgresql.conf file) and then executing the psql \copy, but I got the same error as previously.

[Erwin Brandstetter]

@jatobat: That's odd. I actually tested and it works for me under PostgreSQL 9.1.6. It certainly should work as advertised in 9.2 as well!

[jatobat]

Ok, I've restarted everything, reset the postgresql.conf to its original configuration 'iso,mdy'. Connected to the server. Ran SET datestyle = 'ISO,DMY'; and then the \copy command. The import worked fine. Thanks alot for your help.

[PKU]

Old post, but do anyone know how I can import date with the format "yyyyMMddHHmmss"?

[Erwin Brandstetter]

@NandaKumar: Use to_timestamp(). See: stackoverflow.com/a/18919571/939860

[PKU]

@ErwinBrandstetter. Thanks but my requirement is to be able to do that transformation at the time of importing a csv file. Sorry, I did not explicitly mention this point since the post was about importing csv. For now, I have taken a 2 step process of importing it as varchar into a temporary table and then doing a "insert into select" and doing the transformation to to_timestamp. But I am looking to see if there is any direct way of doing it

[Erwin Brandstetter]

@NandaKumar: Please ask your question as question. Comments are not the place.