Re: [E] Re: CSV From Oracle with timestamp column getting errors

["Saha, Sushanta K" <sushanta.saha@xxxxxxxxxxxxxxxxxxx>]

Awesome. Thanks Victor!

.... Sushanta

On Mon, Mar 22, 2021 at 4:47 PM Victor Yegorov <vyegorov@xxxxxxxxx> wrote:

пн, 22 мар. 2021 г. в 21:38, Saha, Sushanta K <sushanta.saha@xxxxxxxxxxxxxxxxxxx>:

\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"

Appreciate any help with this psql command.

I would recommend issuing one of these on the Oracle side *before* taking the CSV snapshot.
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';

Otherwise, you have to load this CSV file in a table, that has `text` type for the column and do a post-processing,

smth like:

    INSERT INTO permanent_tab

    SELECT *, to_timestamp(col, 'DD-MON-YY HH12.MI.SS.SSSSS AM') FROM temp_table;

Hope this helps.

--

Victor Yegorov

--

Sushanta Saha|MTS IV-Cslt-Sys Engrg|WebIaaS_DB Group|HQ - VerizonWireless
O 770.797.1260  C 770.714.6555 Iaas Support Line 949-286-8810