psql \copy stops with 'missing data for column' while loading a CSV file
ERROR: 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.
Fix it fast
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.csv2Match what you see, then jump to its fix
- 1
The failing line shows as one single value, such as id;name;emailThe file uses another delimiter than the one COPY expectsFix - 2
The error names a column near the end of the table, on every lineThe file has fewer or more columns than the tableFix - 3
The 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
- 1
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.csv2Fix
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
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.csv2Fix
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
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.csv2Fix
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.csvConfirmed by postgresql.org
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
Databases
COPY fails on a CSV with invalid input syntax for an integer column
ERROR: invalid input syntax for integer
COPY stops at the first value it cannot convert.
4 causes, each with a fixDatabases
Importing a CSV into PostgreSQL fails with Permission denied
Permission denied (CSV import through pgAdmin Import/Export)
COPY with a file name is read by the database server, not by pgAdmin or your computer.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fix