10

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?

Community
  • 1
  • 1
jatobat
  • 749
  • 3
  • 8
  • 21

3 Answers3

11

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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. – jatobat Dec 26 '12 at 16:49
  • 1
    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. – Erwin Brandstetter Dec 26 '12 at 16:52
  • 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. – jatobat Dec 26 '12 at 17:09
  • @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! – Erwin Brandstetter Dec 26 '12 at 17:31
  • 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. – jatobat Dec 26 '12 at 17:46
  • Old post, but do anyone know how I can import date with the format "yyyyMMddHHmmss"? – PKU Aug 22 '17 at 22:28
  • @NandaKumar: Use `to_timestamp()`. See: https://stackoverflow.com/a/18919571/939860 – Erwin Brandstetter Aug 22 '17 at 23:46
  • @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 – PKU Aug 24 '17 at 17:47
  • @NandaKumar: Please ask your question as *question*. Comments are not the place. – Erwin Brandstetter Aug 24 '17 at 22:57
4

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' ...."
homat
  • 83
  • 1
  • 6
0

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

SET datestyle TO German;
Noel Cosgrave
  • 9
  • 2