How to Govern AI Agent Access to Enterprise Data

ERROR 22P02: Invalid Input Syntax for Type in Postgres

Error Message

ERROR: invalid input syntax for type integer: "abc"
SQLSTATE: 22P02

Other common variations:

ERROR: invalid input syntax for type uuid: "not-a-uuid"
ERROR: invalid input syntax for type boolean: "yes"
ERROR: invalid input syntax for type timestamp: "04-13-2026"
ERROR: invalid input syntax for type json: "{ invalid }"

What Triggers This Error

PostgreSQL 22P02 fires when a value cannot be parsed into the expected data type. Unlike MySQL, Postgres does not silently coerce types — it rejects bad input immediately. The fix depends on which type conversion failed:

  • String where integer expected — a query passes text like 'abc' to an integer column or parameter
  • Malformed UUID — a string that doesn't match the UUID format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
  • Invalid boolean value — using unrecognized strings like 'Y', 'active', or 'enabled' instead of PostgreSQL's accepted boolean literals
  • Timestamp format mismatch — date string doesn't match the expected format or datestyle setting
  • Invalid JSON literal — malformed JSON in a json or jsonb column

Fix by Scenario

String passed where integer expected

The most common cause. Usually happens when application code passes unsanitized user input directly into a query, or when a query parameter binding fails.

-- This fails
SELECT * FROM users WHERE id = 'abc';
-- ERROR: invalid input syntax for type integer: "abc"

-- Also fails with empty strings
SELECT * FROM users WHERE id = '';
-- ERROR: invalid input syntax for type integer: ""

Fix:

  1. Validate input in the application layer before querying:
# Bad: passes raw input to query
user_id = request.args.get('id')  # could be 'abc' or ''
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# Good: validate first
user_id = request.args.get('id')
try:
    user_id = int(user_id)
except (ValueError, TypeError):
    return {"error": "Invalid user ID"}, 400
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
  1. If null or empty values are possible, handle them explicitly:
-- Use NULLIF to convert empty strings to NULL
SELECT * FROM users WHERE id = NULLIF(:input, '')::integer;
  1. If the column should accept mixed types, check with a regex first:
SELECT * FROM users
WHERE :input ~ '^\d+$'
  AND id = :input::integer;

Malformed UUID

UUID columns are strict — the value must be exactly 32 hex digits in the 8-4-4-4-12 format.

-- These all fail
SELECT * FROM sessions WHERE session_id = 'not-a-uuid';
SELECT * FROM sessions WHERE session_id = '12345';
SELECT * FROM sessions WHERE session_id = '';

Fix:

  1. Validate UUID format before querying:
import uuid

def is_valid_uuid(val):
    try:
        uuid.UUID(str(val))
        return True
    except ValueError:
        return False

session_id = request.args.get('session_id')
if not is_valid_uuid(session_id):
    return {"error": "Invalid session ID"}, 400
  1. In SQL, use a safe cast to avoid the error:
-- Returns NULL instead of raising an error (PostgreSQL 16+)
SELECT * FROM sessions
WHERE session_id = CAST(:input AS uuid DEFAULT NULL ON ERROR);

For PostgreSQL versions before 16, create a safe cast function:

CREATE OR REPLACE FUNCTION try_cast_uuid(text)
RETURNS uuid AS $$
BEGIN
  RETURN $1::uuid;
EXCEPTION WHEN invalid_text_representation THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM sessions WHERE session_id = try_cast_uuid(:input);

Invalid boolean value

PostgreSQL accepts true/false, t/f, y/n, yes/no, on/off, 1/0 (all case-insensitive) — but not arbitrary strings.

-- These work
SELECT * FROM users WHERE active = true;
SELECT * FROM users WHERE active = 'yes';
SELECT * FROM users WHERE active = 'Y';
SELECT * FROM users WHERE active = '1';

-- These fail
SELECT * FROM users WHERE active = 'active';
SELECT * FROM users WHERE active = 'enabled';
SELECT * FROM users WHERE active = 'TRUE1';

Fix:

  1. Map application values to PostgreSQL booleans:
bool_map = {'active': True, 'inactive': False, 'enabled': True, 'disabled': False}
active = bool_map.get(request.args.get('active'))
if active is None:
    return {"error": "Invalid boolean value"}, 400
cursor.execute("SELECT * FROM users WHERE active = %s", (active,))
  1. In SQL, use a CASE expression for non-standard values:
SELECT * FROM users
WHERE active = CASE :input
  WHEN 'active' THEN true
  WHEN 'inactive' THEN false
  WHEN 'enabled' THEN true
  WHEN 'disabled' THEN false
END;

Timestamp format mismatch

PostgreSQL parses timestamps based on the datestyle setting. The default ISO, MDY interprets 01-02-2026 as January 2nd, but 13-01-2026 fails because there's no month 13.

-- Check your current datestyle
SHOW datestyle;  -- e.g., 'ISO, MDY'

-- This fails with MDY datestyle (no month 13)
SELECT '13-01-2026'::date;
-- ERROR: invalid input syntax for type date: "13-01-2026"

-- This works — ISO 8601 format is always unambiguous
SELECT '2026-01-13'::date;

Fix:

  1. Always use ISO 8601 format (YYYY-MM-DD) — it works regardless of datestyle:
-- Always safe
SELECT * FROM events WHERE created_at > '2026-04-13';
  1. Use TO_DATE or TO_TIMESTAMP with an explicit format:
SELECT TO_DATE('13-01-2026', 'DD-MM-YYYY');
SELECT TO_TIMESTAMP('04/13/2026 15:30', 'MM/DD/YYYY HH24:MI');
  1. In application code, format dates before sending:
from datetime import date
# Always send ISO format
cursor.execute("SELECT * FROM events WHERE created_at > %s", (date(2026, 4, 13).isoformat(),))

Invalid JSON literal in jsonb column

Inserting or casting malformed JSON into a json or jsonb column triggers 22P02.

-- These fail
SELECT '{ name: "test" }'::jsonb;       -- keys must be quoted
SELECT '{ "name": undefined }'::jsonb;  -- undefined is not valid JSON
SELECT '{ "name": "test", }'::jsonb;   -- trailing comma is not valid JSON

-- This works
SELECT '{ "name": "test" }'::jsonb;

Fix:

  1. Validate JSON in the application before inserting:
import json

data = request.get_json()
try:
    json_str = json.dumps(data)  # ensures valid JSON
except (TypeError, ValueError) as e:
    return {"error": f"Invalid JSON: {e}"}, 400

cursor.execute("INSERT INTO configs (data) VALUES (%s::jsonb)", (json_str,))
  1. If you need server-side validation, create a helper function:
CREATE OR REPLACE FUNCTION try_cast_jsonb(text)
RETURNS jsonb AS $$
BEGIN
  RETURN $1::jsonb;
EXCEPTION WHEN invalid_text_representation THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Returns NULL instead of raising an error
SELECT try_cast_jsonb('{ invalid }');

Prevention

  • Always use parameterized queries with proper type bindings — let the database driver handle type conversion
  • Validate user input at the application boundary before it reaches SQL
  • Use ISO 8601 format (YYYY-MM-DD, YYYY-MM-DDTHH:MM:SS) for all date and timestamp values
  • For UUID columns, validate format client-side before querying
  • In PostgreSQL 16+, use CAST(... DEFAULT ... ON ERROR) for safe type conversion

Bytebase's SQL Review can enforce column type consistency across your schema, catching potential type mismatches before they cause runtime errors. See also ERROR 42804: Datatype Mismatch for type errors in expressions and assignments.

Edit this page on GitHub
Contact Us