invalid_text_representation invalid input syntax for type numeric: “…” — 22P02

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

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

Related & next steps

• SQLSTATE 22P02 — Invalid Text Representation (full reference)
• Message: invalid input syntax for type integer
• Glossary: Numeric type

Reference: PostgreSQL 18 Section 8.1 “Numeric Types”.