sqlpostgresqltypesdatetime-formatpostgresql-copy

Data type for date in PostgreSQL

I've exported a CSV file from Excel which has date in format ddmmyyyy hmm. I'm using the COPY function to import to a table in PostgreSQL.

Since I only want to retain the date part I tried the date data type:

CREATE TABLE Public."ride_details"(ride_id int,created_at date)

COPY Public."ride_details" FROM '/tmp/ride_details.csv' DELIMITER ',' csv HEADER;

But that resulted in:

ERROR: date/time field value out of range: "26/07/19 5:48"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: COPY ride_details, line 2, column created_at: "26/07/19 5:48"
SQL state: 22008

Do I need to specify a different data type or how to make this work?

Solution

  • COPY is rather unforgiving with invalid input. (This way it can be fast an reliable.)

    It may be enough to set a matching datestyle setting:

    SET datestyle = 'ISO,DMY';  -- DMY being the relevant part

    ... and retry. (Sets the setting for your session only.) Related:

    Info in your question is not entirely clear, you may have to do more:
    copy to a temporary "staging" table with a text column, and INSERT to the actual target table from there using to_date() - with a custom pattern specifying your non-standard date format:

    CREATE TABLE public.ride_details(ride_id int,created_at date);  -- target table

CREATE TABLE pg_temp.step1(ride_id int, created_at text);  -- temp staging table

COPY TO pg_temp.step1 ...;

INSERT INTO public.ride_details(ride_id, created_at)
SELECT ride_id, to_date(created_at, 'DD/MM/YY')  -- or whatever
FROM   pg_temp.step1;

    to_date() ignores dangling characters after the given pattern, so we do not have to deal with your odd hmm specification (hh?).

    I went with the YY format displayed in the error msg, not the yyyy you claim at the top. Either way, the input must be in consistent format, or you need to do more, yet ...

    All in a single DB session, since that's the scope of temp tables. The temp table is not persisted and dies automatically at the end of the session. I use it for performance reasons.

    Else you need a plain table as stepping stone, which is persisted across sessions and can be deleted after having served its purpose.

    Related: