PostgreSQL Error 22P02

SQLSTATE

22P02

Condition name

invalid_text_representation

Product

PostgreSQL

Versions

PostgreSQL 13 to 18; pg_input_is_valid from PostgreSQL 16 (examples run on PostgreSQL 18.6 in PGlite)

Category

Data Exceptions

Last reviewed

24 October 2026

What it means

SQLSTATE 22P02 means PostgreSQL tried to read a text value as another type, such as integer, numeric, uuid, boolean or json, and the text is not valid for that type.

Whenever text becomes a typed value, PostgreSQL parses it with the type's input function: a quoted literal stored into an integer column, a text column cast with ::integer, a parameter sent as a string, a value compared with a typed column, or a field read by COPY. If the text does not follow the type's syntax, the statement fails with 22P02 (invalid_text_representation). The message quotes the exact value, so "" means an empty string and "12.5" means a decimal sent to an integer column.

The rules are strict: integer input accepts optional spaces and a sign but no decimal point, exponent or thousands separator, and numeric uses a full stop as the decimal point regardless of locale. Invalid JSON, a value missing from an enum and a malformed array literal raise the same SQLSTATE with their own wording. Dates and times are different: invalid input syntax for type date is 22007 and a value such as 2026-13-01 is 22008, not 22P02.

Common causes

• An empty string from a form or CSV field is written into a number column; PostgreSQL does not treat '' as NULL.
• A text column in a staging table holds placeholders such as n/a, - or unknown, and is cast to integer or numeric.
• Numbers are formatted for people: 1,5 with a decimal comma, 1,000 with a thousands separator, 12.5 for an integer column, or a currency symbol.
• A malformed id is compared with a uuid or integer column, for example a route parameter that is not a valid uuid.
• JSON is built by string concatenation and is invalid, or a value is not one of the enum's labels.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT qty_text, count(*) AS rows_with_value
FROM staging_orders
WHERE NOT pg_input_is_valid(qty_text, 'integer')
GROUP BY qty_text
ORDER BY rows_with_value DESC;

SELECT * FROM pg_input_error_info('n/a', 'integer');

SELECT id, qty_text
FROM staging_orders
WHERE qty_text !~ '^\s*[+-]?[0-9]+\s*$';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO orders (id, qty)
SELECT id, nullif(trim(qty_text), '')::integer
FROM staging_orders
WHERE pg_input_is_valid(nullif(trim(qty_text), ''), 'integer')
   OR nullif(trim(qty_text), '') IS NULL;

UPDATE staging_prices
SET price_text = replace(price_text, ',', '.')
WHERE price_text ~ '^[0-9]+,[0-9]+$';

UPDATE staging_prices
SET price_text = NULL
WHERE price_text IN ('n/a', '-', 'unknown');

ALTER TYPE mood ADD VALUE 'happy';

Examples

An empty string into an integer column

CREATE TABLE orders (id int PRIMARY KEY, qty int);

INSERT INTO orders (id, qty) VALUES (2, '');

ERROR:  invalid input syntax for type integer: ""

Casting a staging column that holds a placeholder

The first row converts; the statement fails at the first value that does not, and nothing is returned. Use the read-only check above to list every bad value at once.

CREATE TABLE staging_orders (id int, qty_text text);
INSERT INTO staging_orders VALUES (1, '5'), (2, 'n/a');

SELECT id, qty_text::integer AS qty FROM staging_orders;

ERROR:  invalid input syntax for type integer: "n/a"

A decimal comma into a numeric column

CREATE TABLE products (id int PRIMARY KEY, price numeric(8,2));

INSERT INTO products (id, price) VALUES (1, '1,5');

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

Invalid JSON into a jsonb column

JSON keys must be in double quotes: '{"a":1}'. The message says json even for a jsonb column, and the SQLSTATE is 22P02.

CREATE TABLE events (id int PRIMARY KEY, payload jsonb);

INSERT INTO events (id, payload) VALUES (1, '{a:1}');

ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: {a...

How to prevent it

Load external data into a staging table of text columns, check it with pg_input_is_valid (or a pattern on older versions), convert in one INSERT ... SELECT with explicit casts and NULLIF, and keep rejected rows for review instead of failing the whole load.

In applications, validate input at the edge and bind parameters with their real types. Treat 22P02 from user-supplied values as a client error (bad request), not a server fault. Remember that the error aborts the current transaction, so later statements fail with 25P02 until you roll back.

Common questions

Learn more on CodeWithSQL

• SQL data types explained
• CAST function
• SQL NULL values explained

Official documentation

• PostgreSQL error codes (Appendix A)
• Numeric types (input syntax)
• System information functions (pg_input_is_valid, pg_input_error_info)
• JSON types

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.