PostgreSQL error: invalid input syntax for type

SQLSTATE 22P02 (invalid_text_representation) · Updated July 9, 2026

ERROR:  invalid input syntax for type integer: "abc"

A textual value had to be converted to a typed one — integer, uuid, date, numeric — and could not be parsed. The message quotes the offending value, which usually identifies the source immediately: a header row, an empty string, or data that was never validated.

What this error means

This fires wherever text meets a type: explicit casts ('abc'::int), parameters bound as text, COPY reading a file, or implicit conversions in comparisons. Note what it is not: a NULL problem (NULL casts fine to anything) — it is a value that exists and is malformed for the target type. The empty string is the classic: ''::int fails, because empty is not zero.

Common causes

• Casting user input or scraped data without validation.
• Empty strings for numeric fields from forms or APIs that send "" instead of null.
• CSV imports: the header row read as data, a wrong delimiter gluing columns together, or locale formats (comma decimals, D/M/Y dates) the server does not expect.
• IDs of the wrong shape — a numeric ID where a uuid column expects xxxxxxxx-xxxx-....

How to diagnose it

• The quoted value in the message is the diagnosis: "abc" says dirty data, "" says empty-string handling, "id" says header row.
• For imports, find the bad rows by loading into text first (next section) and running the cast as a query — you get all offenders, not just the first.

How to fix it

• Empty strings → convert to NULL at the boundary: NULLIF(value, '')::int.
• Imports → load into a staging table with text columns, then cast in a controlled INSERT ... SELECT, where you can clean, filter and log:

  COPY staging FROM '/tmp/data.csv' WITH (FORMAT csv, HEADER);
  INSERT INTO target (id, amount)
  SELECT id::uuid, NULLIF(amount, '')::numeric FROM staging;

• Validate at the application boundary so bad values are rejected with a useful message before they reach a cast.