BYTEA

Introduction to the PostgreSQL BYTEA data type

BYTEA (binary array) stores variable-length sequences of bytes — raw binary data including null bytes and non-printable characters. Maximum size is 1 GB per column value.

Declare a BYTEA column with:

column_name BYTEA

Common use cases: storing cryptographic hashes, digital signatures, encrypted values, small images or thumbnails, and binary file content for small files.

Creating a table and inserting binary data

CREATE TABLE binary_data (
  id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  data BYTEA
);

-- Insert using hex format (recommended)
INSERT INTO binary_data (data) VALUES ('
2345');

-- Retrieve the data
SELECT * FROM binary_data;

-- Output:
-- id |   data
-- ---+----------
--  1 | 
2345

BYTEA output formats

PostgreSQL supports two output formats for BYTEA. The default is hex format:

-- Hex format (default)
SET bytea_output = 'hex';
SELECT 'H656c6c6f'::bytea;
-- H656c6c6f

-- Escape format (older style)
SET bytea_output = 'escape';
SELECT 'H656c6c6f'::bytea;
-- Hello

Common BYTEA functions

-- Get length in bytes
SELECT octet_length('H656c6c6f'::bytea);
-- 5

-- Concatenate two BYTEA values
SELECT 'H65'::bytea || 'l6c6f'::bytea;
-- H656c6c6f

-- Extract a slice (1-based offset)
SELECT substring('H656c6c6f'::bytea FROM 1 FOR 3);
-- H656c

-- Encode to Base64 text
SELECT encode('H656c6c6f'::bytea, 'base64');
-- SGVsbG8=

-- Decode from Base64 back to BYTEA
SELECT decode('SGVsbG8=', 'base64');
-- H656c6c6f

Storing a hash in BYTEA

CREATE TABLE api_keys (
  id         SERIAL PRIMARY KEY,
  key_hash   BYTEA NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Store a SHA-256 hash of an API key
INSERT INTO api_keys (key_hash)
VALUES (decode(md5('my-secret-key'), 'hex'));

-- Look up by hash
SELECT id, created_at
FROM api_keys
WHERE key_hash = decode(md5('my-secret-key'), 'hex');

Production tips

• For files larger than a few megabytes, store them in object storage (S3, GCS) and save the URL or path in a VARCHAR column — BYTEA at that scale increases backup size and memory pressure.
• Use parameterized queries when inserting binary data from application code — your database driver handles proper encoding without manual hex escaping.
• Use encode(data, 'base64') when you need to return binary data as a text payload in an API response.
• For cryptographic hashes, use SHA-256 or stronger instead of MD5 — PostgreSQL provides digest(data, 'sha256') in the pgcrypto extension.

Reference: PostgreSQL documentation — Binary data types.