Greenplum \ postgresql COPY: extra data after last expected column

0
votes

I'm working on a Greenplum DB 4.3 project which is using PostgreSQL version 8.2. I am trying to import data with STDIN using COPY.

The data contains backslashes, commas, pipes and anything else you can think of. These values cannot be altered.

Below is an example of the data:

1000,"A",0,"","LKP","","L",0,"U","None|Local copy",

1001,"B",0,"","LIT","","L",0,"U","[1005]\source\",

The command which I am using:

(psql) \copy cdr.wf_step_props_ist from '/home/gpadmin/temp.csv' CSV FILL MISSING FIELDS DELIMITER ',' NEWLINE 'CRLF' QUOTE '"'

(c#) COPY cdr.WF_STEP_PROPS_IST (STEP_ID,PARAM_CODE,OCCUR_NO,VALUE_FILE,EVAL_TYPE,DPC_FAILURE_MSG,DPC_PRIORITY,DPC_SEQ,VALUE_STATE,PARAM_VALUE) FROM STDIN CSV NEWLINE 'CRLF' QUOTE '\"' NULL ' ' DELIMITER ',' FILL MISSING FIELDS

But I keep getting this error:

ERROR: extra data after last expected column (seg11 sdw3:40003 pid=14935) CONTEXT: COPY wf_step_props_ist, line 1: "10003,"FCExtConn",0,"","LKP","","L",0,"U","None|Local copy", 10003,"FCInput",0,"","LIT","","L",0,"U"..."

I attempted this from C# & PSQL CLI on Ubuntu (using COPY with CSV file) and the result is the same.

The answers I have found on stackoverflow don't provide a solution to this problem. Is there anyone who could explain to me why this does not work?

Thanks

1
c#postgresqlgreenplum
you have 10 columns but your csv file has 11.. the last comma is superfluous. - light souls
Why are you loading data with COPY into Greenplum rather than gpload or External Tables with gpfdist? gpload is much, much faster and is easily configurable. - Jon Roberts
I am attempting a single means to transfer data which caters for Greenplum AND Postgres 9+. - zdb
Thanks for the feedback :) - zdb

1 Answers

0
votes

i have executed this with PostgreSQL 9.4.10 and its working:

copy cdr.wf_step_props_ist FROM STDIN CSV DELIMITER ',' QUOTE ''''; < your_file.csv

your data:

1000,"A",0,"","LKP","","L",0,"U","None|Local copy"

1001,"B",0,"","LIT","","L",0,"U","[1005]\source\"

Also look at this link for more info about copy in PostgreSQL 8.2