Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

COPY fails on a CSV with invalid input syntax for an integer column

ErrorERROR: invalid input syntax for integer

Short 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.

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

Fix it fast

  1. 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.csv
  2. 2Match what you see, then jump to its fix

    1. 1invalid input syntax for integer on a line with an empty fieldEmpty fields are read as text, not as NULLFix
    2. 2the failing field is written as "" (two double quotes)Quoted empty strings are not NULLFix
    3. 3CONTEXT names line 1, with a column name where a number should beThe header row is loaded as dataFix
    4. 4a whole line, or a name and number together, shown as the bad valueThe delimiter does not match the fileFix

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 | head

2Fix

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

Did this fix it?

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 | head

2Fix

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

Did this fix it?

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.csv

2Fix

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

Did this fix it?

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.csv

2Fix

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

Did this fix it?

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

Next step

Want this handled for good?

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