Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

psql \copy stops with 'missing data for column' while loading a CSV file

ErrorERROR: missing data for column "email"

Short answer

COPY FROM raises this error when a line has fewer fields than the columns it expects. The usual reasons are a wrong delimiter, a header line that was not skipped, a column list that does not match the file, or a row that really is short. Run it with a column list and the right options, then look at the line the error points to.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Note the line number and text that psql prints with the error, then open that line of the file. What it looks like tells you which cause below applies.

    \copy users FROM 'users.csv' WITH (FORMAT csv, HEADER true)
    -- then, in a shell, print the line the error named (here line 1):
    -- sed -n '1,3p' users.csv
  2. 2Match what you see, then jump to its fix

    1. 1The failing line shows as one single value, such as id;name;emailThe file uses another delimiter than the one COPY expectsFix
    2. 2The error names a column near the end of the table, on every lineThe file has fewer or more columns than the tableFix
    3. 3The failing line is line 1, or a single line deep in the fileThe header line was read as data, or one row is genuinely shortFix

What does the error look like?

  • ERROR: missing data for column "email"

Cause 1 of 3

The file uses another delimiter than the one COPY expects

1Check

COPY splits fields on a tab in text format and on a comma in CSV format. A file exported from a spreadsheet in some locales uses a semicolon, so every line looks like one field.

head -n 3 users.csv

2Fix

Give the delimiter explicitly. It must be one single-byte character.

\copy users FROM 'users.csv' WITH (FORMAT csv, DELIMITER ';', HEADER true)

3Verify

The command prints COPY and a row count.

SELECT count(*) FROM users;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The file has fewer or more columns than the table

1Check

Without a column list, COPY expects one field per table column, in table order. A table with more columns than the file has needs a column list that names only the file columns.

\d users
head -n 2 users.csv

2Fix

Name the table columns that the file actually holds, in file order.

\copy users (name, email) FROM 'users.csv' WITH (FORMAT csv, HEADER true)

3Verify

Load again and check a few rows.

SELECT * FROM users ORDER BY id DESC LIMIT 5;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The header line was read as data, or one row is genuinely short

1Check

Without HEADER the first line is loaded as a row. If only one later line fails, that row has fewer fields than the others.

awk -F',' 'NF != 3 { print NR": "$0 }' users.csv

2Fix

Add HEADER true so the first line is discarded. For a short row, repair it in the file; a value that contains the delimiter, the quote character or a line break must be wrapped in the quote character.

\copy users FROM 'users.csv' WITH (FORMAT csv, HEADER true, QUOTE '"')

3Verify

The command finishes without an error and the row count equals the data lines in the file.

SELECT count(*) FROM users;
-- shell: wc -l users.csv

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Always write the column list and the FORMAT, DELIMITER and HEADER options in the \copy command instead of relying on defaults.
  • Export the file with the same tool and settings each time, and open the first lines before loading.
  • Load into a staging table first, then move the rows with INSERT ... SELECT.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.