22P02

At a glance

22P02 means a text input routine cannot interpret a value as the requested type. This page selects the ordinary integer input path and also marks the COPY/text boundary; COPY, enum, extension, and contrib callers can use different primary messages.

Meaning

For a cast, assignment, or text COPY field, the destination type's input routine decides whether text is valid. PostgreSQL 18.6 emits invalid input syntax for type integer: "%s", while the locked PostgreSQL 10.23 source uses invalid input syntax for integer: "%s"; both are dynamic templates, not a universal localized string. Other fixed callers include COPY reject-limit reporting, enum input, and extension validation, so the primary text and context can differ.

Diagnosis

Record the destination type, the exact value after client encoding and parameter binding, and whether the operation is a cast, assignment, text COPY field, enum input, or another type wrapper. Distinguish malformed text (22P02) from numeric range overflow (22003), invalid date/time syntax (22007), invalid binary representation (22P03), and a malformed COPY file/header or framing (22P04). Do not diagnose a binary COPY payload with the text-input message alone.

Response

Validate at the input boundary and preserve the intended target type. Retry only after correcting the value; do not silently truncate or turn the value into NULL unless that is the explicit business rule. In an explicit transaction, an input ERROR leaves the transaction aborted (25P02); use ROLLBACK or a savepoint such as ROLLBACK TO SAVEPOINT convert_input before continuing. A COPY error likewise requires transaction recovery before another command.

Observed diagnostics

The fixed ordinary integer path has ERROR severity and the version-specific primary templates above. The selected runtime records the exact values: "not-an-integer" produced 22P02, and a valid "42" returned 42 on the same autocommit session.

Representative case

The shared registry sends one invalid text cast in autocommit, inspects the real diagnostic and IDLE state, then sends a valid integer text on the same connection. That observed recovery is different from an explicit transaction, where the client must roll back or roll back to a savepoint before continuing. The page uses the registry SQL; the runner substitutes no hidden second definition.

SELECT 'not-an-integer'::integer;
SELECT '42'::integer

The selected PostgreSQL 18.6 and 10.21 runs passed SQLSTATE, severity, state or disconnect recovery, repair, cleanup, and isolated-target stop assertions. See case JSON and authored evidence.

Versions

The locked catalogue records 22P02 from 7.4 through the listed snapshots. The bounded runtime comparison passed on 18.6 and 10.21, with the primary wording differing as shown; that does not generalize every 22P02 caller or every intermediate implementation.

Related

• 22003
• 22007
• 22P03
• 22P04

Sources

• src.errcodes.18.6 — src/backend/utils/errcodes.txt at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba (source).
• src.invalid-integer.18.6 — src/backend/utils/adt/numutils.c at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 9018d559d8a2b04f6d3fa8fedeb754fc5f1f8cd8594292e6adc5108e8f97f1bf (source).
• src.invalid-integer.10.23 — src/backend/utils/adt/numutils.c at 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; blob SHA-256 6cb3fd7e4b38a66c16f2cc4a3de0c52ce44e01dd22edd3dd9340d2a8faea0d35 (source).
• src.copy-text.18.6 — the fixed text-COPY conversion/reject-limit path in src/backend/commands/copyfrom.c at lines 1169-1172 (source).
• src.calls.REL_18_6 / src.calls.REL_10_23 — fixed call scans, SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf / 00d16d3eb01b71ccf1b245c8f3102f9d0ec9f36fb02777b8dd1b99fcb263040c.
• manifest.22P02 / snippet-registry.22P02 — hashes are recorded in evidence/22P02.json and each selected runtime record.

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

• src/backend/utils/errcodes.txt · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/commands/copyfrom.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• contrib/file_fdw/file_fdw.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• contrib/isn/isn.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/commands/extension.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/utils/adt/numutils.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/utils/adt/numutils.c · REL_10_23 ·
  02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4
• verify/cases/22P02/cases.json · ·
• verify/cases/22P02/snippets.json · ·
• raw/calls/REL_18_6.jsonl · REL_18_6 ·
• raw/calls/REL_10_23.jsonl · REL_10_23 ·

Message templates

skipped more than REJECT_LIMIT (%" PRId64 ") rows due to data type incompatibility

cannot cast EAN13(%s) to %s for number: "%s"

invalid character in extension owner: must not contain any of "%s"

invalid input syntax for type integer: "%s"

invalid input syntax for integer: "%s"

Reproduction & repair cases

Recorded runtime evidence

{
  "diagnostic": {
    "text": "invalid input syntax for type integer: \"not-an-integer\"\nLINE 1: SELECT 'not-an-integer'::integer\n               ^",
    "context": null,
    "severity": "ERROR",
    "sqlstate": "22P02",
    "table_name": null,
    "column_name": null,
    "schema_name": null,
    "source_file": "numutils.c",
    "source_line": "618",
    "message_hint": null,
    "datatype_name": null,
    "exception_type": "InvalidTextRepresentation",
    "internal_query": null,
    "message_detail": null,
    "constraint_name": null,
    "message_primary": "invalid input syntax for type integer: \"not-an-integer\"",
    "source_function": "pg_strtoint32_safe",
    "internal_position": null,
    "statement_position": "8",
    "severity_nonlocalized": "ERROR"
  },
  "target_type": "integer",
  "invalid_text": "not-an-integer",
  "repair_value": 42,
  "failed_status": "IDLE",
  "repair_status": "IDLE",
  "connection_autocommit": true
}

{
  "diagnostic": {
    "text": "invalid input syntax for integer: \"not-an-integer\"\nLINE 1: SELECT 'not-an-integer'::integer\n               ^",
    "context": null,
    "severity": "ERROR",
    "sqlstate": "22P02",
    "table_name": null,
    "column_name": null,
    "schema_name": null,
    "source_file": "numutils.c",
    "source_line": "62",
    "message_hint": null,
    "datatype_name": null,
    "exception_type": "InvalidTextRepresentation",
    "internal_query": null,
    "message_detail": null,
    "constraint_name": null,
    "message_primary": "invalid input syntax for integer: \"not-an-integer\"",
    "source_function": "pg_atoi",
    "internal_position": null,
    "statement_position": "8",
    "severity_nonlocalized": "ERROR"
  },
  "target_type": "integer",
  "invalid_text": "not-an-integer",
  "repair_value": 42,
  "failed_status": "IDLE",
  "repair_status": "IDLE",
  "connection_autocommit": true
}