Fix "invalid input syntax for type integer / numeric / date" in Postgres
ERROR: invalid input syntax for type numeric: "$1,200.50"
Somewhere a text value is being converted to a number or a date, and that value doesn't look like one. Postgres shows you the exact value it choked on in the quotes, like "$1,200.50" or "". Fix or skip that value and the conversion works.
Why it happens
- Casting a text column that contains symbols, commas or placeholders like 'N/A' with ::numeric or ::integer.
- Empty strings or spaces in a date column. An empty string is not a date, and it's not NULL either.
- A text fallback for a number column, like COALESCE(manager_id, 'No manager').
- Comparing a column with a value of the wrong kind, like order_date > 'last week'.
How to fix it
1. Strip symbols before casting to numeric
Fails
SELECT vendor, amount_text::numeric AS amount FROM invoice_import_raw;
Works
SELECT vendor, NULLIF(regexp_replace(amount_text, '[^0-9.]', '', 'g'), '')::numeric AS amount FROM invoice_import_raw;
regexp_replace removes $, commas and spaces, which also turns 'N/A' into an empty string. NULLIF then makes empty strings NULL, so they cast cleanly.
2. Turn blank dates into NULL
Fails
SELECT vendor, invoice_date_text::date AS invoice_date FROM invoice_import_raw;
Works
SELECT vendor, NULLIF(TRIM(invoice_date_text), '')::date AS invoice_date FROM invoice_import_raw;
The error shows "", an empty string. TRIM handles values that are only spaces, and NULLIF swaps the empty result for NULL.
3. Cast the number to text for a text fallback
Fails
SELECT name, COALESCE(manager_id, 'No manager') AS manager FROM employees;
Works
SELECT name, COALESCE(manager_id::text, 'No manager') AS manager FROM employees;
COALESCE needs one type for all its values. manager_id is an integer, so Postgres tries to read 'No manager' as an integer and fails. Cast the column to text first.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Parse a text-only importTRIM · NULLIF · regexp_replace · ::numeric · ::date
- Filling in missing managersNULL · COALESCE
- Payments that break the rulesValidation rules · OR · NULL-safe checks