Pivotal Knowledge Base

Follow

Loading Data from a Flat File Results in "ERROR: Extra Data after Last Expected Column"

Environment

  • Pivotal Greenplum Database (GPDB) 4.2.x
  • Operating System- All supported

Symptom

Loading data from flat files result in the following error message:

ERROR: extra data after last expected column

Cause

You will encounter this error if your source data has more columns than the target table.

Resolution

You must ensure that the source data and table columns align.

Example:

-- Target table

ddata=# \d emp
      Table "public.emp"
Column  " Type    " Modifiers
--------+---------+-----------
id      " integer "
dept    " integer "
role    " integer "
Distributed by: (id)

-- Source data file

$ cat /tmp/load.dat
1"2"3"4
1"2"3"4
1"2"3"4
1"2"3"4
1"2"3"4
1"2"3"4

--Error 

ddata=# copy emp from '/tmp/load.dat' with delimiter as '"';
ERROR: extra data after last expected column (seg0 sdw1:55001
pid=12476)
CONTEXT: COPY emp, line 1: "1"2"3"4"

As can be seen from the example above, the source data file has one more column than the target table, resulting in the error described above.

Comments

Powered by Zendesk