How to Fix PostgreSQL Error: Invalid Input Syntax for Type

ERROR: invalid input syntax for type <type>: "<value>" (SQLSTATE 22P02, condition invalid_text_representation) is raised when PostgreSQL is given a text representation that cannot be parsed into the target data type. The error appears on INSERT, UPDATE, COPY, explicit casts (::int, CAST(... AS ...)), and inside WHERE clauses when the comparison forces a cast. The statement fails before any rows change.

What This Error Means

PostgreSQL is strongly typed and refuses implicit conversions that lose information or have no obvious result. When a value arrives as text - from a literal, a parameter, a CSV row in COPY, or a JSON field being unpacked - and the planner needs to convert it to the column's declared type, the type's input function does the parsing. If the input does not match the type's grammar exactly, the input function raises 22P02. Common offenders are empty strings sent to numeric columns, locale-formatted dates, malformed JSON, and casing variations of boolean literals beyond the documented set.

The error is deterministic: the same input always produces the same failure. There is no auto-coercion to NULL or zero. PostgreSQL treats the parse as either successful or fatal.

Common Causes

Empty string sent to a numeric, date, or boolean column - check the application-side serializer (many ORMs send '' instead of NULL).
Locale-formatted dates such as 13/01/2024 for a date column expecting ISO YYYY-MM-DD.
JSON or jsonb column receiving non-JSON text - validate with SELECT 'value'::jsonb.
Boolean column receiving values outside t/f/true/false/yes/no/on/off/1/0 - confirm with SELECT 'maybe'::boolean.
UUID column receiving a non-hex or wrong-length string - the input must match xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.
COPY/\copy with a header row that has not been skipped (HEADER true) - check the COPY options.
Integer column receiving a numeric string with a decimal point or thousands separator ('1,234', '12.0' for int4).

How to Fix `invalid input syntax`

Inspect the offending value. PostgreSQL prints the literal that failed in the error message - copy it into a SELECT cast to reproduce in isolation:
```
SELECT 'abc'::integer;  -- ERROR: invalid input syntax for type integer: "abc"
```
Convert empty strings to NULL at the boundary. Most application crashes come from this single case:
```
INSERT INTO products(quantity) VALUES (NULLIF($1, '')::integer);
```
In the application, send the typed value (or NULL), not an empty string.

Parse dates with to_date/to_timestamp when the input format is not ISO 8601. These functions take an explicit format mask and never depend on DateStyle:

SELECT to_date('13/01/2024', 'DD/MM/YYYY');     -- 2024-01-13
SELECT to_timestamp('Jan 13 2024 10:00 GMT',
                    'Mon DD YYYY HH24:MI TZ');

Validate JSON before storing it. Use the jsonb input function in a CTE to surface the failing row:
```
WITH input(payload) AS (VALUES ($1::text))
SELECT payload::jsonb FROM input;
```
If the payload is from an external source, validate in the application layer first.
Use the documented boolean literals. PostgreSQL accepts (case-insensitive) t, true, y, yes, on, 1, plus their false counterparts. Anything else fails. If you must accept arbitrary strings, normalize in the application or with a CASE expression.

Write a safe-cast helper for messy text columns. A PL/pgSQL function with EXCEPTION WHEN invalid_text_representation returns NULL instead of failing:

CREATE OR REPLACE FUNCTION try_int(t text) RETURNS integer
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    RETURN t::integer;
EXCEPTION WHEN invalid_text_representation OR numeric_value_out_of_range THEN
    RETURN NULL;
END $$;

Use FORCE_NULL and explicit type casts in COPY. COPY does not coerce empty strings to NULL for numeric types by default. Add FORCE_NULL (col1, col2) to do that conversion at the COPY boundary:
```
COPY orders (id, total, customer_id)
    FROM '/tmp/orders.csv'
    WITH (FORMAT csv, HEADER true,
          FORCE_NULL (total, customer_id));
```

Catch `invalid input syntax` Errors Before They Hit Production

Pulse provides AI-powered observability for PostgreSQL alongside Elasticsearch, ClickHouse, and Kafka. For SQLSTATE 22P02 invalid_text_representation errors, Pulse:

Tracks 22P02 rates per source table, column, and application in pg_stat_statements and server logs, so a runaway ingest job dumping malformed rows lights up immediately
Correlates the error with recent application releases, ORM serializer changes, and ETL job runs so root cause maps to a serialization boundary instead of a vague "bad data" report
Flags configuration drift in COPY jobs (missing FORCE_NULL, missing HEADER) and schema changes that introduce stricter typing across replicas and environments before downstream pipelines start failing

In PostgreSQL logs the STATEMENT: line shows the failing SQL and DETAIL: shows the offending value - aggregating these by SQLSTATE, table, and column turns "one user's form broke" into "the v1.42 release's date serializer is emitting the locale format on this endpoint".

Connect your Postgres cluster to surface issues like this proactively.

Preventive Measures

Normalize empty strings to NULL in the application or in a BEFORE INSERT trigger - never let '' reach a numeric or date column.
Use parameterized queries with typed bindings ($1::int, $2::date). The driver does the conversion on the client side and produces clearer errors.
Reject malformed JSON at the API boundary, not at the database.
Add CHECK constraints with ~ regex patterns for free-text columns that must look like UUIDs, dates, or codes.
In COPY, always specify FORMAT csv, HEADER, and FORCE_NULL to make NULL handling explicit.

Frequently Asked Questions

Q: What is SQLSTATE 22P02 in PostgreSQL?
A: 22P02 is the invalid_text_representation SQLSTATE. PostgreSQL raises it when the input function for a data type cannot parse the text representation supplied for a value.

Q: Why does PostgreSQL reject an empty string for a numeric column?
A: PostgreSQL does not treat '' as NULL or zero for numeric types. The integer input function sees an empty string and raises invalid input syntax for type integer: "". Convert empty strings to NULL with NULLIF(val, '') or fix the application's serialization.

Q: How do I import dates that are not in YYYY-MM-DD format?
A: Parse with to_date(text, format) and store the result in a date column, or set DateStyle to the matching style for the session. Avoid relying on DateStyle in production code - explicit to_date is portable across deployments.

Q: Can I make PostgreSQL more lenient with type casts?
A: No. PostgreSQL is intentionally strict about input parsing - implicit conversions that could lose data are removed by design. Wrap casts in a PL/pgSQL function that catches invalid_text_representation and returns NULL, or sanitize at the application layer.

Q: What boolean literals does PostgreSQL accept?
A: PostgreSQL accepts (case-insensitive) t, true, y, yes, on, and 1 for true; f, false, n, no, off, and 0 for false. Anything else raises 22P02. Map application-specific tokens to these in the application code.

Q: Why does my COPY command fail with invalid input syntax on numeric columns?
A: The CSV almost certainly contains empty strings for nullable numeric columns. Add FORCE_NULL (col1, col2, ...) to the COPY options so empty fields are treated as NULL for those columns.

Q: How can I detect PostgreSQL invalid input syntax errors before they break the application?
A: Treat the 22P02 rate per table and column as an SLI and alert on any sustained spike. Pulse tracks 22P02 rates by source table, column, and application, correlates spikes with recent releases and ETL runs, and points at the serialization boundary (ORM, COPY job, form handler) that introduced the malformed input.

PostgreSQL Numeric Field Overflow: the related 22003 error for in-range type mismatches.
PostgreSQL Check Constraint Violated: use CHECK constraints to enforce input shape at write time.
PostgreSQL Character Not in Repertoire: a related text-encoding error.
PostgreSQL Cast Operator: the :: syntax that triggers these conversions.
Common PostgreSQL Errors: index of SQLSTATE codes and conditions.
PostgreSQL COALESCE Function: pattern for substituting NULL for empty input.