postgres \copy command "ERROR: invalid input syntax for type timestamp with time zone" while importing csv data exported from Oracle database

Question

I have exported the oracle database table data in to csv file and importing the same data into postgres database using '\copy' command via command prompt. While importing I'm getting below error because of the timestamp issue

psql command:

\copy "CSV_IMPORT"."DUMMY_TABLE" FROM 'D:\Database_Auto\DUMMY_TABLE_DATA.csv' DELIMITER ',' CSV HEADER;
CSV_IMPORT is the schema name
DUMMY_TABLE is the table name

Error:

ERROR:  invalid input syntax for type timestamp with time zone: "21-JUN-07 06.42.43.950926000 PM"
CONTEXT:  COPY DUMMY_TABLE, line 2, column updated_date: "21-JUN-07 06.42.43.950926000 PM"

If I modify the timestamp data with : instead of . as 21-JUN-07 06:42:43.950926000 PM it is importing the record without any error. I can't do it manually for millions of records in csv file. Any solution via psql command.

Table Create Script:

CREATE TABLE "CSV_IMPORT"."DUMMY_TABLE" ( ID VARCHAR(100) NOT NULL , DOCK_TYPE VARCHAR(1) NOT NULL , START_DATE TIMESTAMP(6) WITH TIME ZONE NOT NULL , UPDATE_SEQ_NBR DOUBLE PRECISION NOT NULL , END_DATE TIMESTAMP(6) WITH TIME ZONE
, CONSTRAINT PK_DUMMY_TABLE PRIMARY KEY ( ID , DOCK_TYPE , START_DATE , UPDATE_SEQ_NBR ) );

Table Data in CSV file:

"ID","DOCK_TYPE","START_DATE","UPDATE_SEQ_NBR","END_DATE"

"756748","L",21-JUN-07 06.42.43.950926000 PM,1,21-JUN-07 06.42.43.950926000 PM

"658399","T",15-NOV-03 02.59.54.000000000 AM,2,15-NOV-03 02.59.54.000000000 AM

"647388","F",19-NOV-04 11.09.05.000000000 PM,3,19-NOV-04 11.09.05.000000000 PM

Welcome to SO. can you add also the CREATE TABLE statement and a sample of the data which is causing your import to fail? — Jim Jones
– Jim Jones, Commented Jul 23, 2020 at 9:35
Hi Jim Jones I have added the Create statemen and sample csv data file FYR — ANTRINITZ SINGH
– ANTRINITZ SINGH, Commented Jul 28, 2020 at 8:42

Mike Organek · Accepted Answer · 2020-07-23 11:33:33Z

0

Your best option is to re-do the export from Oracle and use to_string() to format the timestamp correctly.

If that is not feasible, then change your DUMMY_TABLE column to text instead of timestamptz and use to_timestamp(<tstz_column>, 'DD-MON-YY HH.MI.SS.US000 PM') to parse it inside of PostgreSQL.

If you were not stuck on Windows, you could use \copy ... from program and use sed to clean up your export on the fly.

answered Jul 23, 2020 at 11:33

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ANTRINITZ SINGH Over a year ago

Thanks for your reply. I can't update any data from oracle as it is the original source data. And it is type of archiving the data so altering the column datatype is not permissible. Can you please explain more on "\copy ... from program and use sed to clean up your export on the fly."

Collectives™ on Stack Overflow

postgres \copy command "ERROR: invalid input syntax for type timestamp with time zone" while importing csv data exported from Oracle database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related