Psql: error to import date column using \copy from csv

Question

My csv file is something like this: pmsbcod;ansbfmues;paracod AB070001;2018-10-29;124TMB ...

Using \copy metacommand I always have the error ERROR: invalid input syntax for type timestamp with time zone: "0.0" I don't have problems importing other csv files without timestamp columns

I have tried with several date formats (dd/mm/yyyy, yyyy-mm-dd, yyyy-mm-dd hh:mm:ss, yyyy-mm-dd hh:mm:ss+0.1..) but the error messages is always the same; or ERROR: invalid input syntax for type timestamp with time zone: "2018-10-29 00:00:00+0.1" with this datetime format.

My server date style is ISO, DMY -show datestyle;- In other timestamp columns that I have imported with a python script the date format is 2017-03-21 00:00:00; in the python script I can execute something like cursor.execute('insert into table values (%s, %s, %s)', ('AB070001','2018-10-29','124TMB') with any problems

I'm executing psql from console with code page 1252

Thanks in advance

Is there an CONTEXT line that reports an line number column name for the ERROR? — jjanes
Yes, is the first data line; as I have a header, it report line 2 — luispy

luispy luispy · Accepted Answer · 2020-04-01T08:27:01

The error is not related with date format but with the number of columns in csv -not in the example, but in the real case-

When I write the column names in the \copy metacommand I could catch the cause of the error

I'm so sorry for wasting your time

Psql: error to import date column using \copy from csv

1 Answers