PostgreSQL "invalid input syntax for type integer": find the bad value and handle it

1. The usual sources

An empty form field or query parameter. The app sends '' when a user leaves a field blank, and the query compares it to a number column:

-- ERROR: invalid input syntax for type integer: ""
SELECT * FROM products WHERE stock > '';

Fix it in the app — send NULL (or skip the condition) for an empty field, and validate input before it reaches SQL.

A text column cast to a number. Data stored as text (imported, or from a “flexible” schema) mostly holds numbers — and one row doesn’t:

CREATE TABLE import_raw (sku text, qty text);
INSERT INTO import_raw VALUES ('NET-100', '5'), ('NET-200', ''), ('NET-300', '12 pcs'), ('NET-400', '7');

-- ERROR: invalid input syntax for type integer
SELECT sku, qty::integer FROM import_raw;

Parameters glued into the SQL text. Building queries by concatenating strings ("… WHERE id = '" + id + "'") turns every bad input into an SQL error — and into an SQL injection risk. Use bound parameters, and the driver sends a proper NULL or number.

2. Find the bad rows

Since PostgreSQL 16, pg_input_is_valid tells you whether a value would convert, without failing:

SELECT sku, qty
FROM import_raw
WHERE NOT pg_input_is_valid(qty, 'integer');

On older versions, a regular expression does the same for plain integers:

SELECT sku, qty
FROM import_raw
WHERE qty !~ '^\s*-?\d+\s*$';

3. Convert safely

Decide what the bad values mean, then say it in SQL:

-- Empty means "unknown": NULL
SELECT sku, NULLIF(trim(qty), '')::integer AS qty
FROM import_raw
WHERE pg_input_is_valid(NULLIF(trim(qty), ''), 'integer') OR NULLIF(trim(qty), '') IS NULL;

-- Anything that isn't a number becomes NULL, and stays visible for a later fix
SELECT sku,
       CASE WHEN pg_input_is_valid(qty, 'integer') THEN qty::integer END AS qty,
       qty AS original
FROM import_raw;

You can try a pattern on your own values in the Regex Tester first. Avoid “cleaning” with regexp_replace(qty, '\D', '', 'g'): it turns '12 pcs' into 12, but also '1-2' into 12 and 'v2.0' into 20.

4. Imports: load as text first

When a CSV import fails with this error, the line number is in the message (CONTEXT: COPY products, line 1042, column stock: ""). Two reliable patterns:

• Empty strings as NULL. With COPY … (FORMAT csv), an empty unquoted field is already NULL; a quoted empty string ("") is not. FORCE_NULL (stock) treats both as NULL.
• A staging table. Load everything into text columns, check and convert with the queries above, then INSERT … SELECT into the real table. Bad rows can be reported instead of stopping the whole import.

Also check the obvious: a header line not skipped (HEADER true), columns in a different order than the table, a different delimiter.