How can I import TIMESTAMP into POSTGRES without seconds? Coming from large CSV

Question

Please excuse the newbie POSTGRES question, I'm a little more familiar with MSMSQL, but I'd probably struggle there too.

I have a CSV with approx 12 million rows I am trying to import. One of the fields is a date field in the format "YYYY-MM-DD HH:MM".

My table has the field type set to TIMESTAMP (is this right?). The CSV import fails, stating "ERROR: date/time field value out of range: "2019-20-09 04:00"".

How can I import this type of date string?

My current statements look like:

CREATE TABLE WoSWeatherGrids
(
    id serial NOT NULL,
    Location NUMERIC(6),
    Date TIMESTAMP,
)

COPY WosWeatherGrids(Location,Date) 
FROM '\\location\Weathergrids.csv' DELIMITER ',' CSV HEADER;

The error received is:

ERROR:  date/time field value out of range: "2019-20-09 04:00"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY wosweathergrids, line 2, column date: "2019-20-09 04:00"
SQL state: 22008

I'm not able to change the strings in the CSV, as this is a process that runs automatically from the output of another piece of software that will hopefully occur a couple of times per day. I'm hoping there's a simple method that can be highlighted to allow me to import this string type.

CSV Sample type

Location,Date
1075,2019-20-09 04:00
1075,2019-20-09 05:00
1075,2019-20-09 06:00
1075,2019-20-09 07:00

EDIT: The issue seems to be extending from the fact that Postgres doesn't like YDM DateStyle format. One of the original systems is using MS SQL, and T-SQL can handle that. I cannot modify that system, so still need to deal with this issue.

EDIT2: per the documentation here I've been able to cast an example YYYY-DD-MM HH:SS date type to what I need using the following. select to_char(to_timestamp('2019-20-09 04:00', 'YYYY-DD-MM HH24:MI'), 'HH24:MI DD/MM/YYYY');. I'm still somewhat stuck in that I need to either understand how to do this from the input of the CSV, or how to create a temp table, and then a proper table and cast on the way across.

Answer 1

create a temp column(fec) and after update the date column, example:

CREATE  TABLE WoSWeatherGrids
 (
   id serial NOT NULL,
   Location NUMERIC(6),
   Date TIMESTAMP,
   fec text
 )

COPY WosWeatherGrids(Location,fec) 
FROM '/tmp/datos.csv' DELIMITER ',' CSV HEADER;

update WoSWeatherGrids set date = to_timestamp(fec,'YYYY-DD-MM HH:MI');

alter TABLE WoSWeatherGrids drop COLUMN fec;