Horton
Menu

Re: CSV From Oracle with timestamp column getting errors

Tim Cross <theophilusx@gmail.com>

View thread
From: Tim Cross <theophilusx@gmail.com>
To: pgsql-general@lists.postgresql.org
Date: 2021-03-22T20:56:42Z
Lists: pgsql-general 
"Saha, Sushanta K" <sushanta.saha@verizonwireless.com> writes:

> \COPY table1 FROM '/tmp/Oracle_2020_06.csv' DELIMITER ',' CSV HEADER;
> ERROR:  invalid input syntax for type timestamp: "01-JUN-20 06.04.20.634000 AM"
> CONTEXT:  COPY table1, line 2, column last_update_timestamp: "01-JUN-20 06.04.20.634000 AM"
>

The problem is psql doesn't understand/recognise the timestamp format
being used in the CSV dump from Oracle.

Modify the SQL used to extract the data from Oracle so that it formats
the timestamp as a string which psql can parse into a timestamp type -
for example ISO or any of the psql timestamp formats (see psql manual). 

-- 
Tim Cross