CODEWITHSUNDEEP
postgresqlcsvimportcopycommand
ANTRINITZ SINGH
ANTRINITZ SINGH

Reputation: 31

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

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

Upvotes: 3

Views: 4629

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

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.

Upvotes: 0

Related Questions