COPY fails on a CSV with invalid input syntax for an integer column
ERROR: invalid input syntax for integerShort answer
COPY stops at the first value it cannot convert. For integer columns the usual culprits are an empty field that is not read as NULL, a header row loaded as data, or the wrong delimiter. The error context names the line. Fix it with the CSV, NULL, HEADER, FORCE_NULL or DELIMITER options, or clean the file.
Fix it fast
1Find out which cause you have
Read the CONTEXT line under the error. It gives the line number and column. Then print that line from the file to see what value is there.
sed -n '1,3p' data.csv sed -n '57p' data.csv2Match what you see, then jump to its fix
- 1
invalid input syntax for integer on a line with an empty fieldEmpty fields are read as text, not as NULLFix - 2
the failing field is written as "" (two double quotes)Quoted empty strings are not NULLFix - 3
CONTEXT names line 1, with a column name where a number should beThe header row is loaded as dataFix - 4
a whole line, or a name and number together, shown as the bad valueThe delimiter does not match the fileFix
- 1
What does the error look like?
ERROR: invalid input syntax for integer
Cause 1 of 4
Empty fields are read as text, not as NULL
1Check
The default NULL string is \N in text format. Only in CSV format is it an unquoted empty string. If the load uses text format, an empty field is not NULL.
grep -n ',,' data.csv | head2Fix
Tell COPY the file is CSV. Then unquoted empty fields become NULL.
COPY mytable (id, name, qty)
FROM '/data/data.csv'
WITH (FORMAT csv, HEADER true);3Verify
The command reports the row count, and the empty cells show as NULL.
SELECT count(*) FILTER (WHERE qty IS NULL) FROM mytable;Confirmed by postgresql.org
Cause 2 of 4
Quoted empty strings are not NULL
1Check
In CSV, an unquoted empty value is NULL, but a quoted empty value is a zero-length string, and an integer column cannot take that.
grep -n '""' data.csv | head2Fix
Add FORCE_NULL for the integer columns. It turns a quoted empty string into NULL.
COPY mytable (id, name, qty)
FROM '/data/data.csv'
WITH (FORMAT csv, HEADER true, FORCE_NULL (id, qty));3Verify
Load completes with no conversion error.
SELECT count(*) FROM mytable;Confirmed by postgresql.org
Cause 3 of 4
The header row is loaded as data
1Check
Without HEADER, the first line is a normal row, so a text column name hits the integer column.
head -n 1 data.csv2Fix
Set HEADER true to discard the first line, or MATCH to also require that its names match the table's columns in order.
COPY mytable FROM '/data/data.csv' WITH (FORMAT csv, HEADER true);3Verify
The failing line number moves on or the load succeeds.
SELECT * FROM mytable LIMIT 3;Confirmed by postgresql.org
Cause 4 of 4
The delimiter does not match the file
1Check
The default delimiter is a comma in CSV and a tab in text format. A semicolon or tab file read with the wrong one puts several fields in one column.
head -n 2 data.csv2Fix
Name the delimiter, a single one-byte character.
COPY mytable FROM '/data/data.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ';');3Verify
Row counts match the file's line count minus the header.
SELECT count(*) FROM mytable;Confirmed by postgresql.org
How do I stop it coming back?
- Load into a staging table with text columns first, then cast and clean with SQL.
- Always give FORMAT and HEADER explicitly rather than relying on defaults.
- Export from the source with the same NULL convention that COPY expects.
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
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 fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix