postgresql - ERROR: extra data after last expected column - COPY -

when try import data delimiter | receive error:

error: data after last expected column

i able load data if remove double quote or single quote filed have issue in below sample data requirement need data without removing any.

this copy command:

copy public.dimingredient '/users//downloads/archive1/test.txt'  delimiter '|' null ''  csv header escape '"'  ; 

my table:

  public.dimingredient (     dr_id integer not null,     dr_loadtime timestamp(6) without time zone not null,     dr_start timestamp(6) without time zone not null,     dr_end timestamp(6) without time zone not null,     dr_current boolean not null,     casnumber character varying(100) collate pg_catalog."default" not null,     ingredientname character varying(300) collate pg_catalog."default" not null,     matchingstrategy character varying(21) collate pg_catalog."default",     percentofconfidence double precision,     disclosurestatus character varying(42) collate pg_catalog."default",     issand character varying(1) collate pg_catalog."default",     sandmeshsize character varying(20) collate pg_catalog."default",     sandquality character varying(20) collate pg_catalog."default",     isresincoated character varying(1) collate pg_catalog."default",     isartificial character varying(1) collate pg_catalog."default",     constraint dimingredient_pkey primary key (dr_id) ) 

my data:

5144|2016-07-01 13:34:25.1001891|1900-01-01 00:00:00.0000000|9999-12-31 23:59:59.9999999|true|93834|"9-octadecenamide,n,n-bis(2-hydroxyethyl)-, (9z)"|"no cas match found"||disclosed|||||  5145|2016-07-01 13:34:25.1001891|1900-01-01 00:00:00.0000000|9999-12-31 23:59:59.9999999|true|93834|"9-octadecenamide,n,n-bis-2(hydroxy-ethyl)-,(z)""|"no cas match found"||disclosed||||| 

omitting empty line in dample data, different error message 9.6, wit:

error:  unterminated csv quoted field context:  copy dimingredient, line 3: "5145|2016-07-01 13:34:25.1001891|1900-01-01 00:00:00.0000000|9999-12-31 23:59:59.9999999|true|93834|..." 

strangely enough, error message has been there since csv copy introduced in version 8.0, wonder how data different data show above.

the error message explained: there odd number of quotation characters (") in second line.

since 2 doubled quotes in quoted string interpreted single double quote (" escaped ""), fields in second line are:

5145 2016-07-01 13:34:25.1001891 1900-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999 true 93834 9-octadecenamide,n,n-bis-2(hydroxy-ethyl)-,(z)"|no cas match found||disclosed||||| 

... , copy hits end of file while parsing quoted string. hence error.

the solution use number of " characters per field.

if need " character in field, either choose different quote or quote field , double ".