Обсуждение: CSV From Oracle with timestamp column getting errors
\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"
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"
Thanks & Regards
.... Sushanta
The table column definition:
Column | Type |
---------------------------+--------------------------------+
last_update_timestamp | timestamp(6) without time zone |
---------------------------+--------------------------------+
last_update_timestamp | timestamp(6) without time zone |
Thanks & Regards
.... Sushanta
On Mon, Mar 22, 2021 at 4:37 PM Saha, Sushanta K <sushanta.saha@verizonwireless.com> wrote:
\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.Thanks & Regards.... Sushanta
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
пн, 22 мар. 2021 г. в 21:38, Saha, Sushanta K <sushanta.saha@verizonwireless.com>:
\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';
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
"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
Awesome. Thanks Victor!
.... Sushanta
On Mon, Mar 22, 2021 at 4:47 PM Victor Yegorov <vyegorov@gmail.com> wrote:
пн, 22 мар. 2021 г. в 21:38, Saha, Sushanta K <sushanta.saha@verizonwireless.com>:\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_tabSELECT *, 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