Search code examples
postgresqlcsvtimestampdbeaver

Problem importing CSV Timestamp Column into postgresql - Invalid Format?

I am trying to import a CSV file into a Windows 14.2 version of postgresql, using dBeaver v23.0.0 and its UI Import process, but receive the following error:

ERROR: invalid input syntax for type timestamp: "04 Feb 2022 09:18:23:667"

I have tried defining the destination database column as both timestamp and timestamptz but that did not make a difference. The date/time string looks good to me, and the same CSV file imports into MS SQL Server just fine (sigh).

What am I missing?

Solution 
  • show datestyle; 
SQL, MDY 

select '04 Feb  09:18:23:667 2022'::timestamp;  
invalid input syntax for type timestamp ... 

select '04 Feb  09:18:23.667 2022'::timestamp; 
02/04/2022 09:18:23.667.

    The issue is the : in ... 23:667 .... Not sure how MS SQL Server deals with that as the examples I see all use ..

    The solutions are:

    1. Change that : to . in the file.

    2. Import the CSV data into a staging table and then transfer to final table using something like:

    select to_timestamp('04 Feb  09:18:23:667 2022', 'DD Mon HH:MI:SS:MS YYYY');
02/04/2022 09:18:23.667 PST

    Where to_timestamp() comes from Data Formatting Functions. On that page look for Table 9.27. Template Patterns for Date/Time Formatting to see what the template patterns mean.