invalid input syntax for type numeric: “…”

SQLSTATE 22P02 condition invalid_text_representation class 22 — Data Exception severity ERROR

Reproduced & verified on PostgreSQL 14.23, 15.18, 16.14, 17.10 and 18.4 — identical message on every version.

Last reviewed 30 May 2025 · Reproduced live with the SQL on this page.

Symptoms

A value being converted to numeric/decimal is not a valid number. PostgreSQL raises SQLSTATE 22P02 (invalid_text_representation) and quotes the offending text.

Common when an empty string, currency symbol, or thousands separator reaches a numeric column.
The bad value appears in quotes in the message.
Often surfaces during COPY/import or parameter binding.

What the server log shows

ERROR:  invalid input syntax for type numeric: "1,234.56"

Why PostgreSQL raises this — what the manual says

Section 8.1.2 Arbitrary Precision Numbers:

“The type numeric can store numbers with a very large number of digits.”

The numeric input function accepts an optional sign, digits, a single decimal point, and optional exponent. Grouping separators, currency symbols, blanks, or empty strings are not valid numeric literals and produce 22P02.

Common causes

Thousands separators (1,234) or currency symbols ($, €).
Empty string instead of NULL for a missing number.
Locale-formatted numbers (comma as decimal point).
Stray whitespace or text mixed into the value.

How to fix it

Strip formatting before insert: remove separators/symbols in the application or import step.
Map empty strings to NULL rather than passing them to numeric.
Use to_number(text, format) for locale-formatted input.

Related & next steps

Reference: PostgreSQL 18 Section 8.1 “Numeric Types”.