ERROR: "invalid input syntax for type DATE" while importing data in PostgreSQL

Question

I am importing data in PostgreSQL database from .LOD files(which contains data in csv format). For importing I am following these steps:

1) Right Click On table name

2) click on import

3) select file from which data has to be imported

4) done

But while doing this I am getting an error message for a table with data like:

APP_PGM_APPLY,APP_PGM_CS_NUM,APP_PGM_REQ_DT,APP_PGM_STS,APP_PGM_STS_DT,CR_TS_HI,CR_TS_LO,EFF_BEG_DT,EFF_END_DT,VOID_SW,APP_PGM_NUM
"RE","TD877E3","20150105","DN","20150105","   362952774","   304895920","20150105",          ,   ,"2H01889"                       
"RE","B1ZBA17","20150130","PE","20150130","   363222251","   178702340","20150130","20150204",   ,"2I15600"

The error message is:

enter image description here

both date fields are allowed to accept null values. But still it is throwing this error. Table defination:

CREATE TABLE app_pgm_choice
(
  app_pgm_apply character(2),
  app_pgm_cs_num character(7),
  app_pgm_req_dt date,
  app_pgm_sts character(2),
  app_pgm_sts_dt date,
  cr_ts_hi integer NOT NULL,
  cr_ts_lo integer NOT NULL,
  eff_beg_dt date,
  eff_end_dt date,
  void_sw character(1),
  app_pgm_num character(7) NOT NULL,
  CONSTRAINT app_pgm_choice_pk1 PRIMARY KEY (app_pgm_num, cr_ts_hi, cr_ts_lo)
)

Please help me to solve this issue.

What is the data format of the column in that table ?? or just show the table structure in the question — Vivek S.
– Vivek S., Commented Mar 11, 2015 at 5:37
A guess : the suspect will be the null values here 304895920","20150105", , ,"2H01889" — Vivek S.
– Vivek S., Commented Mar 11, 2015 at 5:40
yeah the null values at this position are giving error. But why? My table should accept null values — Madhusudan
– Madhusudan, Commented Mar 11, 2015 at 5:41

Vivek S. · Accepted Answer · 2015-03-11 09:38:14Z

1

Try copy using this

copy tablename from 'd:/folder/myfile.csv' delimiter ',' csv WITH NULL AS 'null';

I dint import a .lod file but you can try above script by replacing 'd:/folder/myfile.csv' with 'd:/folder/myfile.lod' or renaming your .lod file to .csv

edited Mar 11, 2015 at 9:38

answered Mar 11, 2015 at 5:42

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

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

Comments

Madhusudan · Accepted Answer · 2015-03-11 09:29:14Z

0

After struggling for long time I got the solution. I changed my .LOD file content in the following manner (replaced date column data ' ' with '' OR I can say I removed white spaces for date column)

APP_PGM_APPLY,APP_PGM_CS_NUM,APP_PGM_REQ_DT,APP_PGM_STS,APP_PGM_STS_DT,CR_TS_HI,CR_TS_LO,EFF_BEG_DT,EFF_END_DT,VOID_SW,APP_PGM_NUM
"RE","TD877E3","20150105","DN","20150105","   362952774","   304895920","20150105",,   ,"2H01889"                       
"RE","B1ZBA17","20150130","PE","20150130","   363222251","   178702340","20150130","20150204",   ,"2I15600"

And I run the following command:

COPY app_pgm_choice FROM 'E:\Japser\Jasper Reports\Data\APP_PGM_CHOICE.LOD' CSV HEADER;

Its working fine :)

answered Mar 11, 2015 at 9:29

Madhusudan

4652 gold badges11 silver badges26 bronze badges

Collectives™ on Stack Overflow

ERROR: "invalid input syntax for type DATE" while importing data in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related