Question 232990 in PostgreSQL Question posted in
The official documentation can be found here.

Error trying to import datetime value into a Postgresql table

I have a csv file with several columns having a datetime value with a format like this: ‘2008-12-03-18.01.46.000916’. I am trying to import the data using the import capability built in DBeaver and also using postgresql COPY statement. Both fail.

I am using ‘yyyy-MM-dd-HH.mm.ss.SSS’ as import format in DBeaver and I don’t see a way to tell COPY to use a datetime format to read it. I tried DATEFORMAT as you see here but it doesn’t work:

COPY myTable FROM '/tmp/myDataWithDotsInTime.csv'
WITH (FORMAT csv, HEADER false, DELIMITER ',',  DATEFORMAT 'yyyy-MM-dd-HH.mm.ss');

Postgresql function to_datetime uses a different format than DBeaver and works, but I don’t find a way to tell COPY to use it:

SELECT CURRENT_TIMESTAMP, to_timestamp('2008-12-03-18.01.46.000916', 'YYYY-MM-DD-HH24.MI.SS') AS ts
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was able to use DBeaver by adding extra SSS in the format for the microseconds in the end (in the import settings) to meet the input criteria like this:

    yyyy-MM-dd-HH.mm.ss.SSSSSS

    Thanks to DBeaver support person.

    Also thanks to Stefanov.sm for your work-around solution that works with the COPY statement. However, In my case I prefer the DBeaver solution because I can import my CSV file from my PC instead of having to copy the CSV file to the Linux back-end to use COPY.


  2. 0

    In similar cases (specific formats or a transformation is needed) I use the approach below, generic enough and usually solves the issue.

    • Create a temporary table similar to mytable with the timestamp column as text;
    • copy into the temporary table;
    • insert into mytable with a select from temporary table.

    Assuming that the target field in mytable is ts

    create temporary table the_temporary_table (like myTable);
alter table the_temporary_table alter ts type text;

COPY myTable -- Your COPY statement w/o DATEFORMAT 
FROM '/tmp/myDataWithDotsInTime.csv'
WITH
(
 FORMAT csv, 
 HEADER false, 
 DELIMITER ','
);

insert into myTable
 select <..fields before ts..>,
        to_timestamp(ts, 'YYYY-MM-DD-HH24.MI.SS.US') ts,
        <..fields after ts..>
 from the_temporary_table;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search