PostgreSQL COPY FROM csv with different date format

Question

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.

@S-Man apologies that was just me typing off the top of my head. I am just copying from CSV with no Date format specified, I'll update for correctness. Thanks. — MattP
– MattP, Commented Feb 23, 2021 at 15:15
Could it be that your database is expecting 2020-12-31 instead of 31-12-2020? You could always import it to a text column and cast it to timestamp afterwards ;-) Would it be an option? Although postgres would cast it withnout any problems: SELECT '31-12-2020 08:09'::timestamp ;-) — Jim Jones
– Jim Jones, Commented Feb 23, 2021 at 15:21
@JimJones yes I believe PostgreSQL is expecting 2020-12-31 format but I don't know if this can be manipulated with the COPY FROM function. I assume using a temporary text column will still be considerable more efficient than having Python do a line-by-line file read & insert — MattP
– MattP, Commented Feb 23, 2021 at 15:29
It is indeed much more efficient to parse and transform data once they are inside of the database. And you're right: using INSERT statements would be terribly inefficient with large files. — Jim Jones
– Jim Jones, Commented Feb 23, 2021 at 15:32

Adrian Klaver · Accepted Answer · 2021-02-23 15:54:35Z

5

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

edited Feb 23, 2021 at 15:54

answered Feb 23, 2021 at 15:42

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MattP Over a year ago

Thanks. I created temporary text columns to get the data loaded then in pgAdmin I used "UPDATE public.my_table SET actual_datetime_col=(SELECT str_datetime_col::timestamp)" to update.

Adrian Klaver Over a year ago

You could save a step by just temporarily changing the datestyle setting in the session before you do the COPY.

Collectives™ on Stack Overflow

PostgreSQL COPY FROM csv with different date format

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related