Date in table is dd.mm.yyyy - Can't import to postgres via csv

Question

I'm trying to add a .csv to a table in database.

All dates in the .csv is in this format dd.mm.yyyy ( 18.10.2017).

I'm importing via pgadmin and always get an invalid input error.

I've tried to use almost all date formatting options for the column but without any luck.

I would rather not change the csv manually.

Can anyone help me with this?

Solution 1

I almost always import data into a staging table where all the columns are strings.

Then I use queries to load the final table.

This has several advantages:

• It gives me much more control over how the data is transformed.
• It makes it easier to debug problems -- the entire staging table can be queried to find all rows with a particular issue (for instance).
• Additional validations can be performed before loading into the final table.

This is just a suggestion, but you might find that overall this takes less time.

Solution 2

The DateStyle setting is probably set to MDY. You can check this by running:

show datestyle;

Although dd.mm.yyy isn't listed as a standard input format, if you expect it to work, you will need the DateStyle to line up with the ordering here (DMY).

The date/time style can be selected by the user using the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client.

See section "Date Order Conventions":

https://www.postgresql.org/docs/current/static/datatype-datetime.html