PostgreSQL 22P03 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22P03: invalid binary representation

PostgreSQL error 22P03 invalid binary representation occurs when the server receives data in a binary format that does not conform to the expected internal binary structure of a given data type. This commonly happens during binary-mode COPY operations, improper bytea input, or when a client driver sends malformed binary protocol data. Unlike its text counterpart (22P02), this error specifically targets the binary wire protocol and binary I/O functions.

---

Top 3 Causes and Fixes

1. Using Wrong Format in Binary COPY

Loading a text or CSV file using FORMAT BINARY is the most common trigger for this error. PostgreSQL's binary COPY format requires a strict 11-byte file signature header that plain text files do not have.

-- ❌ Wrong: loading a text file as binary
COPY orders FROM '/tmp/orders.txt' WITH (FORMAT BINARY);

-- ✅ Fix: specify the correct format explicitly
COPY orders FROM '/tmp/orders.txt' WITH (FORMAT TEXT, DELIMITER ',');

-- ✅ Or use CSV format
COPY orders FROM '/tmp/orders.csv' WITH (FORMAT CSV, HEADER true);

-- ✅ Correct binary round-trip (export then import)
COPY orders TO   '/tmp/orders.bin' WITH (FORMAT BINARY);
COPY orders FROM '/tmp/orders.bin' WITH (FORMAT BINARY);

---

2. Malformed bytea Input

PostgreSQL accepts bytea values in hex format (prefixed with \x) or legacy escape format. Passing an invalid hex string or a corrupt byte sequence will trigger 22P03.

-- ❌ Wrong: invalid hex characters
INSERT INTO attachments (data) VALUES ('\xGGHH');

-- ✅ Fix: use valid hex-encoded bytea
INSERT INTO attachments (data) VALUES ('\xDEADBEEF');

-- ✅ Use decode() for safe conversion from a hex string variable
INSERT INTO attachments (data) VALUES (decode('DEADBEEF', 'hex'));

-- ✅ Verify before inserting with a validation check
DO $$
DECLARE v_hex TEXT := 'DEADBEEF';
BEGIN
  IF v_hex ~ '^[0-9A-Fa-f]+$' AND length(v_hex) % 2 = 0 THEN
    INSERT INTO attachments (data) VALUES (decode(v_hex, 'hex'));
  ELSE
    RAISE EXCEPTION 'Invalid hex string: %', v_hex;
  END IF;
END;
$$;

-- ✅ Convert between bytea and hex safely
SELECT encode('\xDEADBEEF'::bytea, 'hex');   -- bytea → hex string
SELECT decode('DEADBEEF', 'hex');            -- hex string → bytea

---

3. Client Driver Binary Protocol Mismatch

When drivers like JDBC or psycopg2 send data using the binary wire protocol, version mismatches or unsupported types (composite types, arrays, domains) can produce binary representations the server cannot parse.

-- Check binary I/O functions registered for types
SELECT t.typname,
       recv.proname AS receive_func,
       send.proname AS send_func
FROM pg_type t
LEFT JOIN pg_proc recv ON t.typreceive = recv.oid
LEFT JOIN pg_proc send ON t.typsend    = send.oid
WHERE t.typname IN ('bytea', 'json', 'jsonb', 'uuid', 'numeric');

-- Force text-mode casting as a temporary workaround
SELECT val::text::numeric FROM some_table;

-- Identify active sessions that may be sending bad binary data
SELECT pid, usename, application_name, client_addr, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start DESC;

Driver-level fix: In psycopg2, disable binary transfer for problematic types:

import psycopg2
import psycopg2.extras

# Disable binary protocol for a specific type if needed
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)

---

Quick Prevention Tips

1. Always explicitly declare FORMAT in COPY statements.
Never rely on defaults. Write FORMAT TEXT, FORMAT CSV, or FORMAT BINARY explicitly in every COPY command, and add a format validation step in your ETL pipeline before hitting production.

2. Standardize binary data handling with PostgreSQL built-in functions.
Always use encode() and decode() for bytea transformations in SQL, and use your driver's official binary serialization API — never manually construct raw byte strings. Add input validation (regex check on hex strings, length checks) at the application boundary before any database write.

---

Related Errors

Code	Name	Description
22P02	invalid_text_representation	Text-mode equivalent — invalid cast like 'abc'::integer
22000	data_exception	Parent class covering all data value errors
42804	datatype_mismatch	Type mismatch often co-occurring with binary protocol issues
22021	character_not_in_repertoire	Encoding boundary errors near binary/text conversion

---

📖 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.