How to fix Invalid binary representation in PostgreSQL

-- 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');

-- 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';

-- 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);

// 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')]
);

-- 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);