Code Beautifier
ERROR: invalid input syntax for type bigint

Fix "invalid input syntax for type bigint" in PostgreSQL

PostgreSQL refused a value for a bigint column: an empty cell, a number with a comma or a unit, or a code with leading zeros. The CSV import fixes.

Input that triggers it

id,zip,amount
1,02134,"1,250"
2,10001,
Open CSV to SQL on its own page
Draft saved locally.

Local workspace

Named projects in IndexedDB · Local only — never synced to our servers. Worksp…

Open manager

Batch workspace

Format multiple files locally in one run.

Dialect:
Delimiter:
Rows per INSERT:
csv
Formatted Outputsql

What the error means

PostgreSQL validates every value against its column type before storing it. invalid input syntax for type bigint: "1,250" means a value arrived for a bigint column that cannot be read as a 64-bit integer, and the message quotes the offending text. It appears during INSERT, COPY, and UPDATE, and it stops the whole statement — one bad row fails the batch.

In the example, three different values would each trigger it depending on how the columns were typed: 02134 if zip is numeric (it parses, but the leading zero is lost, which is a different bug), 1,250 because of the thousands separator, and the empty amount on row two, because an empty string is not a number.

Why it happens

  • Codes typed as numbers. Postal codes, phone numbers, account numbers, and SKUs look numeric but are identifiers. Storing them as bigint drops leading zeros and rejects any that contain a letter or a dash.
  • Formatted numbers: thousands separators (1,250), currency symbols ($40), units (12kg), or a trailing percent sign.
  • Empty cells. A CSV cell with nothing in it is an empty string, and '' is not a valid bigint. It needs to become NULL.
  • Decimal values in an integer column: 18.25 fails for bigint; it needs numeric or double precision.
  • Whitespace or a BOM attached to the first value of the file.

How to fix it

  1. Paste the CSV into the converter above. It infers a type per column from every row, not just the first, and it deliberately keeps leading-zero values as text rather than guessing they are numbers.
  2. Review the generated CREATE TABLE. If a column that should be numeric came out as text, one of its values is not a clean number — find it and fix the data, or accept text if it is really a code.
  3. Check that empty cells became NULL in the INSERT statements, not ''.
  4. For real quantities with separators, clean the source (1250, not 1,250) or import as text and cast with replace(col, ',', '')::bigint afterwards.

For the example, the sound schema is:

CREATE TABLE import (
  id bigint,
  zip text,
  amount bigint
);

with amount imported as 1250 and NULL.

If it still fails

  • COPY reports the line number of the failing row; INSERT ... VALUES with many rows does not, so import in smaller batches to locate it.
  • The MySQL equivalent is Incorrect integer value or, in strict mode, Data truncated for column; SQL Server says Conversion failed when converting the varchar value. Same data, same fix.
  • Nothing pasted into the converter leaves your browser, so a customer export can be checked here without redaction.

Related errors