PostgreSQL 22P02 invalid_text_representation: malformed input

Note: Follow the documented order. An error code identifies the affected system, but it does not prove by itself which part has failed.

1. 1

   Inspect the protocol Position, Detail, and Data type name error fields

   Tools required

   Who:

   You or your application developer reviewing the failed query log or client exception.

   How:

   Read the ErrorResponse fields returned with C = 22P02, specifically M (Message), D (Detail), P (Position, the 1-based character index into the original query string), and d (Data type name) if present, after issuing ROLLBACK if your interactive transaction block is aborted.

   Result that matters:

   The Position index and message text pinpoint which cast expression or parameter failed type conversion.

   If Position points to an explicit :

   : or CAST() expression or a specific VALUES slot, that isolates the exact target data type and input token that failed parsing.

   If the error instead reports Internal position (p) and Internal query (q) with a Where (W) stack trace, that confirms the invalid cast occurred inside a PL/pgSQL function or trigger rather than the top-level SQL string.

   Evidence to keep:

   Record the SQLSTATE 22P02 message, Detail line, Position character index, and a redacted copy of the query template.

   Never paste unredacted passwords, connection strings, tokens, or personal data from failed SQL parameters into shared issue trackers.

   Evidence: PostgreSQL 18 Appendix A: Error Codes · PostgreSQL error and notice fields · PostgreSQL ROLLBACK

2. 2

   Test the rejected text value read-only with pg_input_is_valid and pg_input_error_info

   Tools required

   Who:

   Your developer or DBA running a read-only diagnostic SELECT in psql.

   How:

   Pass a redacted sample of the offending string and the target type name to SELECT pg_input_is_valid('sample', 'integer'); and SELECT * FROM pg_input_error_info('sample', 'integer'); (substituting your target type).

   Result that matters:

   Section 9.27.7 provides these data validity checking functions to test whether a string is valid input for a data type and inspect sql_error_code, message, detail, and hint without aborting a write workload.

   If pg_input_error_info returns sql_error_code 22P02, that confirms the literal string itself violates the lexical format of the target data type.

   If pg_input_error_info returns 22003 (numeric_value_out_of_range) or NULL (valid input), that rules out a 22P02 syntax failure on that sample and points to a different row or parameter in the batch.

   Evidence to keep:

   Save the output row from pg_input_error_info showing message, detail, hint, and sql_error_code.

   Run only read-only SELECT checks first; do not alter table columns to unconstrained text just to silence 22P02.

   Evidence: PostgreSQL 18 Appendix A: Error Codes · PostgreSQL 18 system information functions

3. 3

   Check expression and parameter types with pg_typeof before casting

   Tools required

   Who:

   Your developer inspecting the query parameters or staging table columns.

   How:

   Run SELECT pg_typeof(expr) on the source column or parameter expression in a safe test query, and verify whether empty strings ('') or whitespace strings are arriving where SQL NULL is required.

   Result that matters:

   Knowing the resolved OID type name from pg_typeof and whether blank strings are present shows why the input conversion routine was invoked.

   If the source contains empty strings ('') or stray spaces being cast to integer, numeric, boolean, or uuid, that confirms the input must be trimmed or converted with NULLIF(trim(val), '') before casting.

   If pg_typeof shows two incompatible columns compared or joined because of misordered $1/$2 parameters, that points to a parameter-binding bug in the client code.

   Evidence to keep:

   Keep a screenshot or log of the pg_typeof result and the sanitized sample values that reproduced 22P02.

   Do not concatenate raw user input into SQL strings to bypass parameter binding; always keep parameterized queries.

   Evidence: PostgreSQL 18 lexical structure · PostgreSQL 18 system information functions · PostgreSQL 18 numeric types

A code is meaningful only inside the right product and version context.

In Appendix A of the PostgreSQL 18 documentation, 22P02 maps to invalid_text_representation under Class 22 (Data Exception). When a query casts a string via type 'string', 'string'::type, CAST('string' AS type), or assigns text to a typed table column, the server passes that text to the data type's input conversion routine. If the characters do not match the lexical grammar of the target type, such as letters or an empty string sent to an integer column, the routine aborts the statement with 22P02. The code alone proves malformed input text, not table corruption or a server fault.

• Mediumeditorial warning

  Never work around 22P02 by building SQL queries through unescaped string concatenation, dropping column constraints, or converting structured columns to unconstrained text; always keep typed parameters and roll back aborted transactions with ROLLBACK before retrying.

  Evidence: PostgreSQL ROLLBACK · PostgreSQL 18 lexical structure

• Loweditorial warning

  Because PostgreSQL often includes the rejected literal value inside the error message or Detail field for 22P02, scrub personal data, credentials, and secret tokens before exporting client or server logs.

  Evidence: PostgreSQL error and notice fields

1. 1

   PostgreSQL 18 Appendix A: Error Codes

   PostgreSQL Global Development Group · 2026 · official_doc

2. 2

   PostgreSQL error and notice fields

   PostgreSQL Global Development Group · 2026 · official_doc