PostgreSQL — error 22P02 invalid input syntax for type
Fix PostgreSQL 22P02 by matching the literal format to the column type and validating input before the insert.
Symptoms
- invalid input syntax for type integer
- invalid input syntax for type uuid
- The error appears only for certain rows in a batch
- An empty string is inserted into a numeric column
Possible causes
- A string value is inserted into a numeric, uuid or date column without matching format
- An empty or null-like string is passed for a non-text type
- The application serializes a value differently from the column type
- A parameter is passed as text where the column expects a specific type
Diagnosis steps
Read the type and value in the error
psql -c "insert into t (num) values ('abc')"Interpretation
The message names the expected type and the offending input, which shows whether the value is malformed or mistyped.
Cast or validate explicitly
psql -c "select ''::integer"Interpretation
Casting an empty string reproduces the error, confirming that empty inputs cannot become numeric values.
Find the source of the bad value
grep -rn "insert\|execute" src | grep -i "num\|uuid"Interpretation
Tracing the field identifies where an unexpected string enters a typed column.
Root cause
A value was provided whose text representation cannot be parsed as the column type, so the database rejected the input before storing it.
Fix
Validate and convert values before the insert so they match the column type, avoid empty strings for non-text columns, and use the correct parameter types in the driver.
Verify the fix
Rerun the insert and confirm it succeeds, and confirm the stored value matches the intended type and value.
Symptom walkthrough
An insert or query fails with invalid input syntax for a type such as integer or uuid. The literal cannot be parsed as that type, often because an empty string or a non-numeric value reaches a numeric column, or because the driver sends a parameter typed as text into a stricter column. In batch operations the failure may appear only for specific rows, which points at the data rather than the query.
Fix in practice
Validate and convert values before writing so they match the column type, avoid empty strings where a number or date is required, and use the correct parameter types in the client. Cleaning the data upstream is more reliable than leaning on the database to coerce.
Verification
Rerun the insert and confirm it succeeds, then confirm the stored value has the intended type and content. Correct storage confirms the input now matches the column type.
Diagnostic commands
Check the behavior with commands like these:
psql -c "insert into t (num) values ('abc')"
psql -c "select ''::integer"
grep -rn "insert\|execute" src | grep -i "num\|uuid"