Pivotal Knowledge Base

Follow

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

Environment

 Product  Version
 Pivotal Greenplum  4.2.x
 OS  All Supported OS

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