🚨 PostgreSQL Error Guide: 'invalid input syntax for type integer' — Fix

Overview

PostgreSQL raises this when it is asked to turn a text value into an integer and the text isn’t a valid whole number — an empty string, a decimal, text with spaces, or an outright word:

ERROR:  invalid input syntax for type integer: ""

Other common variants name the offending value directly:

ERROR:  invalid input syntax for type integer: "N/A"
ERROR:  invalid input syntax for type integer: "12.5"
ERROR:  invalid input syntax for type integer: "1,000"

The quoted value after the colon is the exact string Postgres tried and failed to parse.

Symptoms

• An INSERT/UPDATE fails when a form field, CSV cell, or API payload carries a non-numeric value into an integer column.
• A WHERE id = $1 query fails because the parameter arrived as a non-numeric string (often "").
• A COPY bulk import stops on a row whose integer column contains an empty string or N/A.
• A query with an explicit ::integer cast or CAST(... AS integer) fails on some rows.
• A join or comparison between a text column and an integer forces an implicit cast that fails.

Common Root Causes

• Empty string instead of NULL — "" is a valid text value but not a valid integer; the classic case is an unfilled optional numeric form field.
• A decimal or formatted number — "12.5", "1,000", "$40", or "10 " with trailing whitespace.
• Non-numeric text — "N/A", "none", "null" (as a literal string), or a header row read as data.
• Wrong column referenced in a cast — casting a text column that holds mixed content to integer.
• Parameter type mismatch — the driver binds a string where the query expects an integer, and the value isn’t purely numeric.
• CSV import quoting — a source system exports empty numerics as "" and the target column is integer.
• Concatenated or templated SQL where a variable interpolated into an integer position is empty.

Diagnostic Workflow

The error message quotes the failing value — start there. If it’s "", you have an empty-string-vs-NULL problem.

If a COPY/import fails, find rows whose integer-bound column isn’t a clean integer. Load into a staging table of text columns first, then test:

SELECT id, amount_raw
FROM import_staging
WHERE amount_raw !~ '^\s*-?\d+\s*$'   -- anything that isn't an optional-signed integer
LIMIT 50;

For an application query, log the statement and its bound parameters to see the real value sent:

SET log_min_error_statement = 'error';   -- failing SQL + parameters land in the log

Inspect the column type you’re inserting into and any casts in the statement:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'payments';

To find bad values already sitting in a text column you plan to cast, test the cast safely before running it for real:

-- rows that would fail a ::integer cast
SELECT ctid, value_text
FROM legacy_table
WHERE value_text IS NOT NULL
  AND value_text !~ '^\s*-?\d+\s*$';

Example Root Cause Analysis

A checkout endpoint began throwing invalid input syntax for type integer: "" intermittently. Enabling log_min_error_statement = 'error' captured the statement:

INSERT INTO orders (customer_id, quantity, coupon_id) VALUES ($1, $2, $3)
parameters: $1 = '4471', $2 = '2', $3 = ''

coupon_id is an integer column, and when a customer applied no coupon the frontend sent an empty string rather than omitting the field or sending JSON null. Postgres accepted 4471 and 2 but rejected '' for the integer column.

The immediate fix was in the application: convert empty numeric inputs to NULL before binding. As a defensive database-side measure, the insert was changed to normalize the value:

INSERT INTO orders (customer_id, quantity, coupon_id)
VALUES ($1, $2, NULLIF($3, '')::integer);

NULLIF($3, '') turns an empty string into NULL (which the nullable coupon_id accepts) while still casting real numbers, so the endpoint stopped failing.

Prevention Best Practices

• Convert empty numeric inputs to NULL at the application boundary; never let "" reach an integer column.
• Use typed query parameters/bindings so the driver sends an integer, not a string, for integer columns.
• For imports, land data in a text staging table, validate with a regex (^\s*-?\d+\s*$), clean, then cast into the typed table.
• Use NULLIF(value, '')::integer (or a safe-cast function) in INSERT ... SELECT and import transforms to absorb empty strings.
• Strip formatting (commas, currency symbols, whitespace) before casting: replace(replace(v, ',', ''), '$', '').
• Validate numeric fields with a CHECK constraint or at the API layer so bad values are rejected early with a clear message.

Quick Command Reference

-- Find values in a text column that won't cast to integer
SELECT * FROM staging WHERE col !~ '^\s*-?\d+\s*$' AND col IS NOT NULL;

-- Safely absorb empty strings during insert/transform
SELECT NULLIF(col, '')::integer FROM staging;

-- Strip common formatting before casting
SELECT replace(replace(trim(col), ',', ''), '$', '')::integer FROM staging;

-- Log the failing statement and its parameters
SET log_min_error_statement = 'error';

-- Check the target column's type
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'orders';

Conclusion

invalid input syntax for type integer is a data-quality error, not a Postgres bug: something handed the parser a string that isn’t a whole number, and the quoted value in the message tells you exactly what. The dominant cause is an empty string where the application should have sent NULL, fixed cleanly with NULLIF(value, '')::integer or better input handling. For imports, stage in text, validate with a regex, and cast only clean rows. Push numeric validation to the application boundary and the runtime failures disappear.