PostgreSQL invalid text representation: find the input boundary

The symptoms

• •Application log shows SQLSTATE 22P02 with a message of the form "invalid input syntax for type <typename>: <short offending token>" originating from a PostgreSQL backend.
• •Server log line contains ERROR: invalid_text_representation alongside the statement text and a caret pointing at the offending column value or literal.
• •Query fails on a CAST, ::type, or implicit comparison (e.g. WHERE text_column = '12-OCT' against an INTEGER cast), while the same value would be accepted as TEXT.
• •An ORM maps string fields to typed columns and throws a wrapping exception (e.g. SequelizeDatabaseError, psycopg2 DataError) that obscures the SQLSTATE; the inner code remains 22P02.
• •pg_stat_statements reports the same query digest failing repeatedly with this SQLSTATE, indicating a persistent input pattern rather than a one-off typo.
• •BI or ETL job aborts with "extra characters after parsing" or "invalid input syntax" when reading CSVs, JSON, or fixed-width feeds into typed staging tables.

Likely causes

• •Application code concatenates user input directly into SQL strings, so untrimmed values (" 42 ", currency symbols, locale thousand separators, or trailing whitespace) reach a typed column or parameter.
• •Schema column type was tightened (e.g. TEXT to INTEGER, TEXT to DATE, TEXT to JSONB) without a backfill, leaving legacy rows that the new cast rejects.
• •Locale-dependent values such as dates or decimals use a format the database does not accept (DD/MM vs MM/DD, comma decimal separator) and are passed without an explicit formatter.
• •Booleans, enums, or UUIDs are sent as strings not in the accepted literal set (e.g. "yes", "Y", "1" for BOOLEAN; non-canonical UUID case; an enum label that no longer exists).
• •JSON columns receive malformed JSON via a SELECT cast or a JSON-producing function, or receive a scalar where an object/array is required by downstream consumers.
• •Numeric columns receive non-finite values (NaN, +/-Infinity) which PostgreSQL only accepts as DOUBLE PRECISION and only via specific entry points, not as NUMERIC.

First ten minutes

1. 01Capture the exact PostgreSQL error fields: SQLSTATE 22P02, the "for type X" fragment, and the offending token shown after the colon; do not rely on the application's outer exception class.
2. 02Identify the failing statement and isolate whether the cast is explicit (CAST or ::), implicit from a comparison, or from a column target during INSERT/UPDATE.
3. 03Walk the input boundary backward: user form field, API request body, message queue payload, file/CSV row, scheduled job argument; record the raw string at that boundary.
4. 04Reproduce the failure with a parameterized statement in psql using the suspected raw string to confirm which cast rejects it and what the accepted form is.
5. 05Decide whether the bad value is one-off (fix the producer's validation for that record) or systemic (fix the producer's serialization or the schema mismatch) before changing any code or data.
6. 06Check pg_stat_activity and pg_stat_statements for other concurrent 22P02 errors on the same digest to confirm whether the boundary condition is shared.

Evidence to collect

• •The full PostgreSQL error message including the "invalid input syntax for type <type>:" prefix and the offending token, plus SQLSTATE 22P02.
• •The original parameterized values or request body for the failing statement, captured at the producer boundary (form input, API handler, queue consumer, ETL reader).
• •Column data types, NOT NULL constraints, DEFAULTs, and CHECK clauses for the destination relation, obtained from information_schema.columns and pg_constraint.
• •Version of PostgreSQL in use and the time_zone, lc_numeric, and lc_messages GUC settings that affect parsing of literals.
• •pg_stat_statements digest and call count for the failing query, plus the most recent sample parameter sets if pg_stat_statements tracks parameters.
• •Sample rows from the staging or source table that demonstrate the rejected pattern, with bytea/hex inspection if the source is binary.

Where to look

• •The producer boundary where strings first enter code: HTTP request handlers, CLI argument parsers, message deserializers, and CSV/JSON readers before any type coercion.
• •Column definitions in the destination table via information_schema.columns and pg_type, looking for types stricter than TEXT (INTEGER, NUMERIC, DATE, TIMESTAMP, BOOLEAN, UUID, JSONB, ENUM).
• •PostgreSQL server log lines tagged with SQLSTATE 22P02 in the relevant time window, paired with the statement text and the application_name from pg_stat_activity.
• •Application exception stack traces where 22P02 appears under a framework-specific wrapper, including the SQL emitted and the bound parameters.
• •Database functions, views, or generated columns that perform explicit casts (CAST(... AS type), ::type) which will surface invalid_text_representation before the row is stored.
• •COPY and \copy bulk-loaders where text-mode input is parsed as typed values; their error reports include the line number and column of the bad token.

Diagnostic steps

1. 01From the captured error, extract the destination type and the offending token; confirm via psql that the same token reproduced against a literal of that type rejects with SQLSTATE 22P02.
2. 02Inspect pg_type and information_schema.columns to confirm the declared column type; mismatches between TEXT and the destination type are a common culprit.
3. 03For DATE/TIMESTAMP errors, check that the literal matches the session's DateStyle and the value against the ISO 8601 form PostgreSQL accepts without ambiguity.
4. 04For NUMERIC/DECIMAL errors, verify the presence of thousand separators, currency symbols, locale decimal commas, leading/trailing spaces, and exponent forms the parser rejects.
5. 05For BOOLEAN errors, restrict accepted inputs to the documented set ("t", "f", "true", "false", "yes", "no", "on", "off", "1", "0") and verify case matches; reject "Y"/"N" and localized tokens.
6. 06For JSONB errors, validate the payload with a JSON parser before sending; many serialization failures look like 22P02 from the database instead of the original parse error.
7. 07For UUID errors, normalize case and strip whitespace before sending; pg_stat_statements and pg_constraint confirm the column type if the cast was implicit.
8. 08Cross-check pg_stat_statements for repeated 22P02 on the same digest; if multiple digests fail, the schema or producer is the boundary, not the row.

Common mistakes

• •Catching the ORM-level exception and retrying with the same string, which masks the cast error and burns CPU without fixing the producer.
• •Loosening the column type to TEXT "as a quick fix," which trades a localized error for silent corruption downstream when consumers assume a stricter type.
• •Setting datestyle, lc_numeric, or timezone on the server to match one client's locale, which then breaks every other client and does not address the input boundary.
• •Stripping non-numeric characters with a regex in the application without first proving that PostgreSQL's parser would still reject the cleaned value; the cast site is the authoritative test.
• •Trusting AI or generated SQL that emits ::type casts without parameter binding; placeholders become literals and a single bad value aborts the batch.
• •Treating COPY failures as transient and re-running the load; the same rows will fail again because the text-mode parser is deterministic about accepted literals.

Safe fixes

• •At the producer boundary, validate raw strings against the destination type before sending, using a library parser (e.g. a date parser, a JSON parser, a UUID parser) rather than relying on PostgreSQL to reject bad data after the fact.
• •When a schema tightening is the cause, write a backfill that converts existing rows safely within the same transaction, then commit; do not leave legacy TEXT values where INTEGER/DATE/JSONB is now expected.
• •For localized numbers and dates, format using an explicit, locale-independent representation (ISO 8601 for dates, no thousand separators for NUMERIC, dot decimal separator) at the producer boundary; document this in the producer contract.
• •For BOOLEAN and ENUM columns, gate inserts on a whitelist of accepted labels in the application; if the label is dynamic, persist the column as TEXT with a CHECK constraint and a lookup table rather than an enum type.
• •For JSONB payloads, reject the request at the API layer when the parsed JSON does not match the expected schema; never depend on the database to surface shape errors as 22P02 in production.
• •For bulk loads, validate rows in a staging TEXT table before moving them to the typed destination; report line numbers back to the producer so they can fix the source, not the database.

Prove the fix

1. 01Re-run the previously failing parameterized statement with the same value through the fixed producer code and observe a successful execution (no SQLSTATE 22P02) recorded in the server log.
2. 02pg_stat_statements shows calls and rows for the same digest completing with no 22P02 count over a defined observation window that previously contained the failure.
3. 03A regression unit test binds the exact offending token as a parameter to the same query and asserts the execution succeeds; a second test asserts a still-invalid token still raises 22P02, proving validation did not silently accept everything.
4. 04Application logs for the producer boundary show a validation failure (or successful parse-and-format) for the same record, confirming the fix is at the input edge rather than a database patch.
5. 05If a backfill was performed, information_schema and pg_type confirm the destination column type and a SELECT count on the affected rows shows all rows now satisfy the column's parse contract.

Prevention and next steps

• •Define the producer contract for each typed column (accepted date format, numeric separators, boolean spellings, JSON shape) in writing, and enforce it in the application before the database call.
• •Use parameterized queries exclusively so values travel as data and the server reports a precise token rather than a SQL-injection-shaped string in the error.
• •Add CI tests that feed canonical bad inputs through the producer and assert 4xx responses, so the database never sees them in staging or production.
• •Review schema changes that tighten column types; require a backfill plan, a data audit, and a rollback path before the migration runs.
• •Monitor pg_stat_statements and the server log for repeated 22P02 on a digest as an early signal that a producer has drifted from the contract.

Safe commands and checks

-- Reproduce the cast rejection against a literal of the destination type:
-- SELECT '12-Oct'::date;
-- Expected: ERROR: invalid input syntax for type date: "12-Oct" (SQLSTATE 22P02)
-- Identify the destination column type and any CHECK constraints:
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = <schema> AND table_name = <table>;
-- Capture the exact error, statement, and offending token from the server log:
-- SELECT application_name, state, query
-- FROM pg_stat_activity
-- WHERE state_change IS NOT NULL;
-- Confirm whether the same query digest has been failing repeatedly:
-- SELECT query, calls, sqlstate
-- FROM pg_stat_statements
-- WHERE sqlstate = '22P02';
-- Stage raw text from a bulk load to validate before typed insertion:
-- CREATE TEMP TABLE staging_text (raw text);
-- \copy staging_text FROM '<path-to-source-file>' WITH (FORMAT csv);
-- SELECT raw FROM staging_text WHERE raw !~* '^[0-9]+$';
-- After a backfill, prove the destination rows now satisfy the type contract:
-- SELECT count(*) FROM <table>
-- WHERE column_with_tightened_type IS NULL
--    OR column_with_tightened_type::text = '';