Menu

PostgreSQL Error 22P02: Invalid Text Representation

Fix PostgreSQL SQLSTATE 22P02 by checking the target type, empty strings, numeric values, and date formats before casting.

Posted on By
On this page

PostgreSQL SQLSTATE 22P02 is invalid_text_representation. It means PostgreSQL could not parse a text value as the requested type, for example when converting not-a-number to integer. The error often includes the target type and offending value. See the PostgreSQL 18 error-code appendix.

Check the target type and the actual input

Start with the full error message and the value being converted. For an INSERT or UPDATE, inspect the destination column type:

SELECT column_name, data_type, udt_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
ORDER BY ordinal_position;

For example, this conversion fails because the text is not a valid integer:

SELECT 'twelve'::integer;

Correct the source value or choose a destination type that matches the data. Do not replace invalid values with 0 or NULL unless that is the intended meaning.

An empty string is not the same as SQL NULL. If an empty form field should mean a missing integer value, convert it deliberately before casting:

SELECT NULLIF('', '')::integer;

NULLIF returns NULL here; use this only when an empty value really represents missing data.

Use an unambiguous date format

PostgreSQL accepts several date formats, and ambiguous values such as 01/02/03 depend on the session’s DateStyle. Check the setting if application and database sessions interpret dates differently:

SHOW DateStyle;

Prefer an unambiguous ISO date literal such as:

DATE '2026-09-27'

If an incoming date uses a known non-ISO layout, parse it with an explicit format using to_date(text, text) rather than relying on session defaults. PostgreSQL documents date input and DateStyle in Date/Time Types.

Validate imported text before casting

For staging tables that hold raw values as text, PostgreSQL 16 and later provide pg_input_is_valid and pg_input_error_info for checking input before converting it:

SELECT raw_quantity,
       (pg_input_error_info(raw_quantity, 'integer')).message AS input_error
FROM staging_orders
WHERE NOT pg_input_is_valid(raw_quantity, 'integer');

These functions support data types whose input functions report invalid values as soft errors. For other types, validation can still abort the transaction just like a direct cast. See PostgreSQL’s data validity checking functions.

Distinguish invalid input from other type errors

  • 22P02 (invalid_text_representation): a value cannot be parsed as the requested type.
  • 22003 (numeric_value_out_of_range): the value is numeric but exceeds the target type’s range.
  • 22007 (invalid_datetime_format): a date/time value does not match an accepted format; see Error 22007 troubleshooting.
  • 42804 (datatype_mismatch): the expression or assignment types are incompatible; see Error 42804.

For other SQLSTATE guides, browse PostgreSQL Error Troubleshooting.