PostgreSQL 22P03 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22P03: invalid binary representation

PostgreSQL error 22P03 invalid_binary_representation occurs when the database receives data in binary format that does not conform to the expected internal binary encoding for a given data type. This error is most commonly encountered during COPY operations with binary format, client driver binary protocol mismatches, or invalid type casting involving binary data.

Top 3 Causes and Fixes

1. COPY with Incorrect Binary Format

Using FORMAT BINARY with a file that isn't a valid PostgreSQL binary dump triggers this error immediately.

-- BAD: Attempting to load a plain text file as binary
COPY employees FROM '/tmp/employees.txt' WITH (FORMAT BINARY);
-- ERROR: 22P03 invalid binary representation

-- GOOD: Use CSV or TEXT format for non-binary files
COPY employees FROM '/tmp/employees.csv' WITH (FORMAT CSV, HEADER true);

-- GOOD: Proper binary round-trip (same PostgreSQL version only)
-- Export
COPY employees TO '/tmp/employees.dump' WITH (FORMAT BINARY);
-- Import
COPY employees FROM '/tmp/employees.dump' WITH (FORMAT BINARY);

-- Verify binary file header signature (should start with 'PGCOPY\n\377\r\n\0')
SELECT substring(pg_read_binary_file('/tmp/employees.dump')::bytea FROM 1 FOR 11);

2. Client Driver Binary Protocol Mismatch

JDBC, psycopg2, or libpq drivers sending incorrectly encoded binary parameters for types like uuid, inet, or jsonb will cause this error.

-- GOOD: Use explicit text casting to bypass binary encoding issues
INSERT INTO test_table (id, ip_address)
VALUES (
    'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid,
    '192.168.1.1'::inet
);

-- Diagnostic: Catch 22P03 in PL/pgSQL for graceful handling
DO $$
BEGIN
    PERFORM 'bad-uuid-value'::uuid;
EXCEPTION WHEN invalid_binary_representation THEN
    RAISE NOTICE 'Caught 22P03: %', SQLERRM;
END;
$$;

-- JDBC workaround: add preferQueryMode=simple to connection URL
-- jdbc:postgresql://host:5432/mydb?preferQueryMode=simple

3. Invalid bytea or Composite Type Handling

Inserting raw binary strings into bytea columns or mismatched composite types causes this error.

-- BAD: Raw string inserted into bytea
-- INSERT INTO files (data) VALUES ('raw binary content');

-- GOOD: Use hex encoding with decode()
INSERT INTO files (data) VALUES (decode('DEADBEEF', 'hex'));

-- GOOD: Use escape literal
INSERT INTO files (data) VALUES (E'\\xDEADBEEF'::bytea);

-- Check and set bytea output format
SHOW bytea_output;
SET bytea_output = 'hex'; -- recommended default

-- Safe cast function to prevent 22P03 from bubbling up
CREATE OR REPLACE FUNCTION safe_uuid_cast(input TEXT)
RETURNS UUID AS $$
BEGIN
    RETURN input::uuid;
EXCEPTION WHEN invalid_binary_representation OR invalid_text_representation THEN
    RAISE WARNING 'Invalid UUID: %', input;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

SELECT safe_uuid_cast('not-a-valid-uuid'); -- returns NULL with WARNING

Quick Fix Checklist

Switch COPY from FORMAT BINARY to FORMAT CSV or FORMAT TEXT when in doubt.
Always regenerate binary dump files after a major PostgreSQL version upgrade.
Force text protocol mode in your driver (preferQueryMode=simple for JDBC, binary=False for psycopg2).
Use explicit ::type casting when inserting data to avoid driver-level binary encoding issues.

Prevention Tips

Validate binary files before loading — Check the PGCOPY header signature before running any binary COPY operation in production.
Standardize on text protocol for cross-version deployments — Reserve binary protocol only for performance-critical, single-version environments where driver and server compatibility is guaranteed and tested.

Related Errors

22P02 invalid_text_representation — The text-format equivalent of this error.
22000 data_exception — Parent class of 22P03.
42804 datatype_mismatch — Often appears alongside binary representation errors during type casting.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community