By jatobat


2012-12-26 15:14:30 8 Comments

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?

3 comments

@Noel Cosgrave 2016-04-26 09:50:22

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

SET datestyle TO German;

@homat 2016-03-05 09:02:37

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' ...."

@Erwin Brandstetter 2012-12-26 15:28:26

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.

@jatobat 2012-12-26 16:49:17

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 2012-12-26 16:52:14

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 2012-12-26 17:09:29

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 2012-12-26 17:31:13

@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 2012-12-26 17:46:21

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.

@KNP 2017-08-22 22:28:19

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

@Erwin Brandstetter 2017-08-22 23:46:23

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

@KNP 2017-08-24 17:47:53

@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 2017-08-24 22:57:17

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

Related Questions

Sponsored Content

16 Answered Questions

[SOLVED] How to import CSV file data into a PostgreSQL table?

2 Answered Questions

2 Answered Questions

2 Answered Questions

[SOLVED] How to set datestyle in postgres db in c#

0 Answered Questions

How to convert postgresql database into csv file from a vb.net program

1 Answered Questions

[SOLVED] My application doesn't seem to understand PostgreSQL datestytle settings

2 Answered Questions

[SOLVED] Best way to prevent duplicate data on copy csv postgresql

2 Answered Questions

[SOLVED] PostgreSQL COPY command issues

1 Answered Questions

[SOLVED] DB2-clob data to PostgreSQL

1 Answered Questions

[SOLVED] psycopg: can't copy from csv to postgresql with python, no results

Sponsored Content