How to fix Invalid binary representation in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 171 views

PostgreSQL error 22P03 occurs when binary data format is invalid or mismatched with the column type. This happens with bytea columns, COPY BINARY operations, or client driver encoding issues.

Quick fix

Fixes Invalid binary representation

-- Correct: hex format with \x prefix (preferred)
INSERT INTO table_name (binary_column) VALUES (E'\xdeadbeef');

-- Correct: escape format with E prefix and double backslashes
INSERT INTO table_name (binary_column) VALUES (E'\\x41\\x42');

-- Incorrect: missing \x prefix or improper escaping
INSERT INTO table_name (binary_column) VALUES ('deadbeef');
  1. Verify bytea column format in SQL
  2. Check client encoding configuration
  3. Fix COPY BINARY format mismatches
  4. Check client driver or ORM settings
postgresql — error
-- Correct: hex format with \x prefix (preferred)
INSERT INTO table_name (binary_column) VALUES (E'\xdeadbeef');
-- Correct: escape format with E prefix and double backslashes
INSERT INTO table_name (binary_column) VALUES (E'\\x41\\x42');
-- Incorrect: missing \x prefix or improper escaping
INSERT INTO table_name (binary_column) VALUES ('deadbeef');
Invalid binary representation

What this error means

PostgreSQL error code 22P03 indicates that the database received binary data in a format it cannot interpret. This typically occurs when inserting or updating bytea (binary) data with incorrect formatting, using binary COPY operations with misaligned data structures, or when client applications fail to properly encode binary values. The error signals a mismatch between what PostgreSQL expects and what it received.

Typical symptoms

  • INSERT or UPDATE statements fail when writing to bytea columns
  • COPY FROM with BINARY format fails mid-import after processing rows successfully
  • ORMs or database drivers throw "incorrect binary data format" errors during query execution
  • Binary data import works on one system but fails on another (encoding/endianness issues)
  • Queries succeed when using text format but fail with binary protocol

Common causes

  1. Incorrect bytea escape or hex format in SQL (missing \x prefix or invalid escaping)
  2. Data type mismatch when using binary COPY: importing data structured for a different column type or byte order
  3. Client encoding mismatch between application and PostgreSQL database connection
  4. Using binary protocol mode in driver without proper serialization of data types
  5. Corrupted or truncated binary data during transmission or import
  6. Endianness (byte order) mismatch when sending raw binary structures

How to fix "Invalid binary representation"

1Verify bytea column format in SQL

When inserting binary data directly in SQL, use the correct format:

sql
-- Correct: hex format with \x prefix (preferred)
INSERT INTO table_name (binary_column) VALUES (E'\xdeadbeef');

-- Correct: escape format with E prefix and double backslashes
INSERT INTO table_name (binary_column) VALUES (E'\\x41\\x42');

-- Incorrect: missing \x prefix or improper escaping
INSERT INTO table_name (binary_column) VALUES ('deadbeef');

The hex format (\xHEXVALUES) is preferred because it's faster and compatible with more external tools.

2Check client encoding configuration

Ensure your PostgreSQL connection encoding matches your application encoding:

sql
-- Check server encoding
SHOW server_encoding;

-- Check client encoding
SHOW client_encoding;

-- Set client encoding if needed (must be done before operations)
SET client_encoding = 'UTF8';

If your application uses a different encoding, either change the application encoding or explicitly convert the binary data before insertion.

3Fix COPY BINARY format mismatches

When using COPY ... FROM BINARY, ensure data structure alignment:

sql
-- Verify your binary file matches the column layout
COPY table_name (col1, col2) FROM STDIN WITH (FORMAT binary);

-- If importing fails, validate with text format first
COPY table_name (col1, col2) FROM STDIN WITH (FORMAT text);

Binary COPY expects strict byte-for-byte alignment with column types (int2 = 2 bytes, int4 = 4 bytes, bytea includes length prefix). If importing from another tool, verify the binary structure matches PostgreSQL's internal format.

4Check client driver or ORM settings

If using an ORM (Prisma, Sequelize, SQLAlchemy) or database driver (libpq, Npgsql, psycopg), verify it's properly encoding binary data:

javascript
// Prisma example: ensure proper Buffer handling
const data = Buffer.from('deadbeef', 'hex');
await prisma.table.create({ data: { binaryColumn: data } });

// Node.js with pg driver
const client = new Client();
await client.query(
  'INSERT INTO table_name (binary_col) VALUES ($1)',
  [Buffer.from('deadbeef', 'hex')]
);

If the error persists, check driver documentation for binary protocol mode settings or consider using text format as a temporary workaround.

5Validate data integrity before import

Test your binary data with a simple query before bulk import:

sql
-- Create a test bytea column with sample data
CREATE TABLE test_binary (id SERIAL, data bytea);

-- Try inserting a small sample
INSERT INTO test_binary (data) VALUES (E'\xdeadbeef');
SELECT * FROM test_binary;

-- If successful, try importing larger dataset in smaller chunks
COPY test_binary (data) FROM STDIN WITH (FORMAT binary);

If the sample succeeds but bulk import fails, the issue is likely data corruption or format mismatch in the larger dataset.

Advanced notes

PostgreSQL bytea supports two legacy input formats: hex (preferred, faster, more compatible) and escape (traditional). The escape format requires the E prefix and double backslashes, making it error-prone. When migrating from MySQL BLOB or other databases, verify the binary representation matches PostgreSQL's expectations.

For binary COPY operations, PostgreSQL uses a strict binary protocol that includes type OIDs (Object IDs) and length prefixes. Misaligned structure (e.g., sending 2 bytes for an int4 field) will cause this error even if the values are semantically correct. If debugging binary COPY failures, enable verbose logging with "SET log_statement = 'all';" and check the PostgreSQL logs for additional context.

Some ORMs switched from text to binary protocol for performance (Npgsql moved to binary by default), which can trigger this error if the driver doesn't properly serialize types. Check the driver version and consider reverting to text mode temporarily while investigating.

Frequently asked questions

What does "Invalid binary representation" mean?
PostgreSQL error code 22P03 indicates that the database received binary data in a format it cannot interpret. This typically occurs when inserting or updating bytea (binary) data with incorrect formatting, using binary COPY operations with misaligned data structures, or when client applications fail to properly encode binary values. The error signals a mismatch between what PostgreSQL expects and what it received.
How do I fix "Invalid binary representation"?
PostgreSQL error 22P03 occurs when binary data format is invalid or mismatched with the column type. This happens with bytea columns, COPY BINARY operations, or client driver encoding issues.
What causes "Invalid binary representation"?
Incorrect bytea escape or hex format in SQL (missing \x prefix or invalid escaping) Data type mismatch when using binary COPY: importing data structured for a different column type or byte order Client encoding mismatch between application and PostgreSQL database connection
Is the "Invalid binary representation" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading