I am trying to load a CSV file to my PGadmin database (on my local computer). I am running PostgreSQL16 with PGadmin4 as the user interface. I am trying to upload the date data as follows:
| UniqueID | Date |
|---|---|
| 1 | 25/01/2023 00:00:00 |
| 2 | 14/07/2014 00:00:00 |
I have set the date column to be a timestamp without a timezone. When I run this script to import the csv file:
\copy public.mytable (UniqueID, Date) FROM 'Directory/Filename.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8' QUOTE '\"' ESCAPE '''';""
I get the following error message:
ERROR: date/time field value out of range: "26/01/2020 00:00" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: COPY salespeople, line 2, column hire_date: "26/01/2020 00:00"
I have tried the following commands:
SET DateStyle TO ISO, DMY;
SHOW DateStyle;
To see the current datestyle in my database and then adapt the datestyle formatting as needed but I can't quite figure out how to make this work. I've tried setting the datestyle to 'dd/mm/yyyy hh:mm:ss' but this code is not recognised.
How can I change the datestyle in my database so that the format of the CSV file is accepted?
