PostgreSQL COPY FROM csv with different date format

user_623117 0
Viewed 1585

I am trying to upload CSV data to a PostgreSQL database in Python using the COPY FROM STDIN function.

In the CSV file my Date field is DD-MM-YYYY HH:MI and this gives me an error:

psycopg2.errors.DatetimeFieldOverflow: date/time field value out of range: "31-12-2020 08:09"

Is there a way I can define the Date/Time format when using COPY FROM?

DB column is type TIMESTAMP if relevant.

I only know how to do this with a line-by-line INSERT statement.

psycopg2 postgresql
1 Answers

Just before the COPY command do:

set datestyle = euro;

show datestyle;
 DateStyle 
-----------
 ISO, DMY

Then this works:

SELECT '31-12-2020 08:09'::timestamp;
      timestamp      
---------------------
 2020-12-31 08:09:00

Otherwise with my default datestyle:

show datestyle;
 DateStyle 
-----------
 ISO, MDY

SELECT '31-12-2020 08:09'::timestamp;
ERROR:  date/time field value out of range: "31-12-2020 08:09"
LINE 1: SELECT '31-12-2020 08:09'::timestamp;

For more information see here Date input Table 8.15. Date Order Conventions

user_70706130
Related