PostgreSQL Invalid Binary Representation (SQLSTATE 22P03)

PostgreSQL raises ERROR: invalid binary representation with SQLSTATE 22P03 (invalid_binary_representation) when a type's binary input function — the internal routine responsible for deserializing a value from wire-format bytes — receives a byte sequence it cannot decode as a valid value for that type. This is distinct from text-format casting errors; it occurs specifically in the binary data path.

What This Error Means

SQLSTATE 22P03 belongs to class 22 — Data Exception — the same class as errors like invalid_text_representation (22P02) and numeric_value_out_of_range (22003). Every PostgreSQL type that supports binary I/O exposes a receive function (e.g., int4recv, float8recv, uuidrecv). These functions parse the raw bytes transmitted over the binary protocol and are stricter than their text counterparts because binary formats encode exact internal representations rather than human-readable strings.

When the byte sequence handed to a receive function does not match the expected binary encoding — wrong length, invalid bit patterns, out-of-range byte values — PostgreSQL immediately raises 22P03. The error surfaces the type name and often the offending byte offset, making it reasonably diagnosable.

After this error is raised the current statement is aborted. If you are inside an explicit transaction block, the entire transaction enters an error state and must be rolled back before any further commands can execute. A connection in simple-query mode outside a transaction block will automatically recover for the next query.

Common Causes

1. COPY ... WITH (FORMAT binary) data mismatch. The binary COPY format is version-specific and type-layout-specific. Feeding a binary COPY file produced by one PostgreSQL major version into a significantly different version, or loading a file that was corrupted or truncated during transfer, is the most common source of this error.

2. Client sending wrong binary encoding over the extended query protocol. When a client uses the PostgreSQL extended query protocol (libpq PQexecParams, JDBC, asyncpg, etc.) and requests binary parameter or result format, it must encode values according to PostgreSQL's internal binary representation. A bug in a driver, a hand-rolled binary encoder, or a mismatch between the declared type OID and the actual bytes sent will trigger 22P03.

3. Corrupt or mismatched bytea or composite type data. Manually constructing binary input for composite types, range types, or array types with incorrect field counts, alignment padding, or element type OIDs produces this error when PostgreSQL tries to deserialize the structure.

4. Using a binary dump/restore across incompatible type layouts. Restoring a pg_dump --format=custom archive (which uses binary COPY internally for some types) onto a PostgreSQL build with a different --with- compile-time option or OS alignment assumption can expose binary incompatibilities.

5. Custom type extension bug. If your schema uses a C-language extension that defines a custom type with a receive function, a bug in that function's input validation may raise 22P03 for certain inputs.

How to Fix invalid_binary_representation

1. Regenerate binary COPY files from the source. If you are using COPY ... WITH (FORMAT binary), re-export from the source database and re-import. Do not transfer binary COPY files between PostgreSQL major versions without verifying type compatibility.

   -- Export using text format when cross-version portability is needed
   COPY my_table TO '/tmp/my_table.csv' WITH (FORMAT text, DELIMITER ',', HEADER true);
   
   -- Import on the target
   COPY my_table FROM '/tmp/my_table.csv' WITH (FORMAT text, DELIMITER ',', HEADER true);
   

2. Switch to text format in the extended query protocol. If your application sends parameters in binary format, switch to text format while debugging. In libpq, set paramFormats to all zeros; in JDBC, check driver-level binary transfer settings:

   // JDBC: disable binary transfer for a specific type if it is misbehaving
   // Add to connection URL:
   // ?binaryTransferDisable=uuid,jsonb
   

   In asyncpg (Python), you can disable binary codec negotiation for a specific type:

   await conn.set_type_codec(
       'uuid',
       encoder=str,
       decoder=str,
       schema='pg_catalog',
       format='text',
   )
   

3. Validate binary payloads before sending. If you are constructing binary-format parameters manually, cross-reference the PostgreSQL source (src/backend/utils/adt/) for the exact byte layout of each type. For example, a uuid in binary format is always exactly 16 raw bytes in network byte order — not a hex string.

   -- To inspect what a valid binary encoding looks like, use send():
   SELECT pg_type.typname, length(typname::text)
   FROM pg_type WHERE typname = 'uuid';
   
   -- Get the binary representation of a known-good value:
   SELECT encode(uuid_send('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid), 'hex');
   -- Returns: a0eebc999c0b4ef8bb6d6bb9bd380a11  (16 bytes)
   

4. Re-dump using pg_dump with --format=plain. If the error appears during a restore from a custom-format dump, use pg_dump --format=plain to produce a SQL-text dump that avoids binary encoding entirely, then restore from that.

   pg_dump --format=plain --no-owner -d mydb > mydb_plain.sql
   psql -d mydb_target < mydb_plain.sql
   

5. Audit custom extension receive functions. If the error references a user-defined type, review the C receive function in your extension. Ensure it validates input length and byte patterns before dereferencing, and calls ereport(ERROR, ...) with ERRCODE_INVALID_BINARY_REPRESENTATION on bad input rather than segfaulting or silently accepting garbage.

Additional Information

• SQLSTATE 22P03 is a PostgreSQL-specific code (the P prefix indicates a PostgreSQL extension to the SQL standard). It is not defined in the SQL standard.
• Closely related codes in class 22: 22P02 (invalid_text_representation) is the text-format analogue; 22000 (data_exception) is the generic parent; 22003 (numeric_value_out_of_range) covers numeric overflow.
• The error was present as far back as PostgreSQL 7.x; the binary protocol and binary COPY format have not changed in their fundamental structure across modern major versions (10–17), but individual type binary representations can differ across major versions for complex types.
• JDBC: The PostgreSQL JDBC driver can trigger this if preferQueryMode=simple is mixed with binary-transfer-enabled connections. The driver's binaryTransferEnable and binaryTransferDisable connection parameters control which types use binary encoding.
• asyncpg: Because asyncpg uses binary protocol by default for all types, misregistered custom codecs are a common source of 22P03 in Python applications.
• pgx (Go): pgx uses binary format by default; type mismatches in ScanRow or Encode implementations will surface as this error.

Frequently Asked Questions

Why does this error only appear in my application but not in psql? psql uses the text protocol for queries by default, which means values go through the text input functions (typinput, not typreceive). Your application almost certainly uses the extended query protocol in binary mode. The same value that parses fine as a text string may be rejected when its binary encoding is malformed.

Can this error appear from a normal INSERT statement in psql? Very rarely. It would require the INSERT to use a binary COPY pipe or for a trigger/function to invoke a binary receive function directly. In practice, 22P03 in interactive SQL almost always comes from COPY ... WITH (FORMAT binary).

What does the error message look like in practice? Common forms include:

ERROR:  invalid binary representation for type uuid
ERROR:  invalid binary representation for type integer
ERROR:  invalid binary representation for type jsonb

The exact message includes the target type name, which tells you which column or parameter is causing the problem.

Is the connection safe to reuse after this error? Yes. 22P03 is a statement-level error. If you were outside a transaction, the connection is fine. If you were inside a transaction, issue a ROLLBACK (or ROLLBACK TO SAVEPOINT) before issuing further statements — the transaction is aborted and PostgreSQL will reject all commands until it is rolled back.