CODEWITHSUNDEEP
postgresqlcsvtimestamppsqlpostgresql-9.2
jatobat
jatobat

Reputation: 759

Import CSV with timestamp format "dd.mm.yyyy hh.mm.ss" using psql \copy

I'm trying to import data from a .csv file into a Postgres 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?

Upvotes: 10

Views: 33761

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

Set datestyle on the server. Issue this SQL command in your session before you running \copy.

SET datestyle = 'ISO,DMY';

You are using the psql meta-command \copy, so the input file is local to the client. But the server 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 on \set:

Note: This command is unrelated to the SQL command SET.

Upvotes: 11

Noel Cosgrave
Noel Cosgrave

Reputation: 9

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

SET datestyle TO German;

Upvotes: 0

homat
homat

Reputation: 93

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

Upvotes: 4

Related Questions