PostgreSQL COPY Error Extra Data After Last Expected Column

You run \copy, PostgreSQL prints ERROR: extra data after last expected column, and the CONTEXT line shows a row that looks fine. The message is accurate and unhelpful: it tells you a line has more fields than the table has columns and nothing about why. There are four causes that account for almost every case I reproduced, and each leaves a different signature in the line it prints. This article walks through them, shows how to count the fields of every line instead of staring at one, and gives the fix. The surrounding procedure is in the PostgreSQL loading guide.

The most common cause and its fix

A trailing delimiter, described in the PostgreSQL COPY reference as one field too many for the column list. Some exporters write a comma after the last field of every row, so a five column file has six fields per line, the last of them empty. PostgreSQL counts the sixth:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'trailing-comma.csv' WITH (FORMAT csv, HEADER true)" 2>&1

ERROR:  extra data after last expected column
CONTEXT:  COPY customers, line 2: "1,Ana,[email protected],2025-01-15,120.50,"

The CONTEXT line shows COPY customers, line 2 and the line itself, ending in 120.50,. That last comma is the signature. The fix is to remove it from the file, with sed 's/,$//' if every line has one and no quoted field ends a line, or to add a throwaway sixth column to the table and drop it after the load, which is safer when you are not sure about the file. Check the header too: in this file the header had five names and the data had six fields, so HEADER match would not have caught it, since it only reads line one.

The causes, in the order to check them

An unquoted comma inside a value

A name like Ana, Silva that was written without quotes splits into two fields. The line is 1,Ana, Silva,[email protected],2025-01-15,120.50, which has six fields:

psql -c "\copy customers FROM 'unquoted-comma.csv' WITH (FORMAT csv, HEADER true)" 2>&1

ERROR:  extra data after last expected column
CONTEXT:  COPY customers, line 2: "1,Ana, Silva,[email protected],2025-01-15,120.50"

The message is the same, and the text of the line shows the problem: a value that contains a comma, with a space after it. The fix belongs at the source, because the writer failed to quote. If you must repair the file and the structure is regular, a script can re-join the fields known to hold text. The quoting article explains why a line like this cannot be repaired in general, and the escaping article has a repair that works when the structure is known.

More columns in the file than in the table

A file with a new column, or a table that was created with too few columns. The line is 2,Bo,[email protected],2025-02-01,9.00,web,extra with seven fields:

psql -c "\copy customers FROM 'wide-row.csv' WITH (FORMAT csv, HEADER true)" 2>&1

ERROR:  extra data after last expected column
CONTEXT:  COPY customers, line 3: "2,Bo,[email protected],2025-02-01,9.00,web,extra"

The same message, for line 3. The header would have told you earlier: a file whose header has more names than the table has columns fails HEADER match at line 1 with wrong number of fields in header line, before any data is read. If the header and the data agree and the table is what is wrong, add the columns. If the extra columns are not wanted, import only some columns through a staging table or a pre-filter.

A NUL byte

A NUL, the byte 00, ends the C string that psql hands to the server, so the text after it vanishes and the next line gets glued on. The error is again extra data after last expected column, and the CONTEXT line shows two records on one line with the text between them missing, such as 1,abc2,plain, where 1,abc and 2,plain have met. If a line in the context looks like two records stuck together, search the file for NUL bytes, as in the article on removing null bytes and control characters. That article also shows the trap in finding them: grep -c without -a reported zero lines for a file with 400 of them.

Find every bad line, not the first

COPY stops at the first error, so a file with three problems costs three runs. Count the fields of every line yourself, once. Python's csv module understands quotes, so it gets the widths right where a simple split does not:

python - <<'PY'
import csv, collections
for name in ("trailing-comma.csv", "unquoted-comma.csv", "wide-row.csv"):
    widths = collections.Counter()
    first_bad = None
    with open(name, newline="") as f:
        for n, row in enumerate(csv.reader(f), start=1):
            widths[len(row)] += 1
            if n > 1 and len(row) != 5 and first_bad is None:
                first_bad = n
    print(f"{name:20} widths={dict(widths)}  first line with a width other than 5: {first_bad}")
PY

trailing-comma.csv   widths={5: 1, 6: 1}  first line with a width other than 5: 2
unquoted-comma.csv   widths={5: 1, 6: 1}  first line with a width other than 5: 2
wide-row.csv         widths={5: 2, 7: 1}  first line with a width other than 5: 3

The output lists the distribution of widths for each of the three files and the first line whose width is not 5. A healthy file shows one width. A file with a single bad row shows two, and the bad one is the one with the lower count. Note that for unquoted-comma.csv the finding is line 2, the same line COPY named, and the width 6 tells you it is one field too many, which is what an unquoted comma does.

The shell version, awk -F,, is quicker and wrong for any file with quoted commas, since it counts the comma inside "Smith, Jane" as a separator:

awk -F, 'FNR > 1 && NF != 5 {print FILENAME ": line " FNR " has " NF " fields"}' trailing-comma.csv unquoted-comma.csv wide-row.csv

trailing-comma.csv: line 2 has 6 fields
unquoted-comma.csv: line 2 has 6 fields
wide-row.csv: line 3 has 7 fields

That output flags the same three lines, because none of my three files has a quoted comma. On a file that does, awk would flag every row with a quoted name. Use it for a first look and trust the Python result, or use qsv with qsv select and qsv count, which read quotes properly. Remember FNR and not NR in awk when you give it several files, or the line numbers run on from one file into the next, which I got wrong on my first attempt.

The related error: missing data

The opposite message, missing data for column "x", means a row has too few fields. It comes from a table with more columns than the file, a truncated last line, or a row where a delimiter was lost. The cure is symmetrical: count widths, find the lines, and fix the file or the table. A file that was cut off mid-record often ends in a line that raises this error, which is one of the few cases where an error is a good sign, since it shows the truncation. If the last record happened to be cut inside its final field, there is no error, and the shortened value loads quietly, as the gzip article demonstrates.

How to stop it happening again

Put the width check in front of the load. A script that parses the file with a CSV reader, counts the widths and fails unless they are all equal takes a few lines and catches this entire family of errors, including the ones COPY would not report until the middle of the file. The strict record count in the load script does it with qsv count, which fails on a ragged row. For files from a partner, ask them to quote every text field and to export with a library, and write the expected column count in the contract.

When the row count is right and the error persists

If every line has the right width by a CSV aware count, and PostgreSQL still complains, the delimiter or the quote character differs from what you told it. A semicolon file loaded with the default comma reports a different error, usually a type error on the first column, so check that you passed DELIMITER ';'. A file using a backslash as an escape inside quotes needs ESCAPE E'\\', as the quoting article shows. And a file with mixed line endings can fail with unquoted newline found in data, which is a different message with a different cause, covered in the line endings article. When the line the error names is not the line you see in your editor, remember that quoted fields can contain newlines and the number counts physical lines.