PostgreSQL Binary String (bytea) Functions

Complete reference for PostgreSQL binary string functions for the bytea data type, covering encoding, decoding, byte-level access, length measurement, and binary manipulation. Includes encode/decode with hex and base64, get_byte, set_byte, and substr on binary data. Updated for PostgreSQL 16.

15 functions

Jump to function

|| (bytea concatenation)bit_length octet_length btrim ltrim rtrim substring overlay position get_byte set_byte encode decode sha256 md5 (bytea)

What are PostgreSQL Binary String (bytea) Functions?

PostgreSQL binary string functions operate on the bytea data type, which stores arbitrary binary data such as images, file contents, and cryptographic hashes. encode() and decode() convert between bytea and text representations (hex, base64, escape). get_byte() and set_byte() provide direct byte-level access by position. Binary strings differ from text strings — length() on bytea returns the byte count, not the character count.

|| (bytea concatenation)

PG 8.0+→ bytea

Concatenates two binary strings.

DeveloperData EngSecurity

Signature

bytea || bytea → bytea

Parameters

Parameter	Type	Description
bytes1	bytea	First binary string
bytes2	bytea	Second binary string

Examples

sql

SELECT '\x123456'::bytea || '\x789a00bcde'::bytea;

→\x123456789a00bcde

sql

SELECT '\x00'::bytea || '\xFF'::bytea;

→\x00ff

sql

SELECT '\xDEAD'::bytea || '\xBEEF'::bytea;

→\xdeadbeef

sql

SELECT header || payload || trailer FROM binary_frames;

→Assembled binary frame

⚠Anti-Pattern

Use `||` to assemble binary protocol frames from component byte sequences in SQL. Combine with `decode('...', 'hex')` or `set_byte` for dynamic byte construction.

example

SELECT '\x01'::bytea || length(payload)::int2::bytea || payload FROM messages;

→Simple binary frame: type byte, 2-byte length, payload

bit_length

PG 8.0+→ integer

Returns the number of bits in the binary string (8 times the number of bytes).

DeveloperData EngSecurity

Signature

bit_length ( bytea ) → integer

Parameters

Parameter	Type	Description
bytes	bytea	Binary string to measure

Examples

sql

SELECT bit_length('\x123456'::bytea);

→24

sql

SELECT bit_length('\xFF'::bytea);

→8

sql

SELECT bit_length('\x0000'::bytea);

→16

sql

SELECT bit_length(data) / 8 AS byte_count FROM binary_records;

→Equivalent to octet_length

⚠Anti-Pattern

`bit_length(b) = 8 * octet_length(b)`. Prefer `octet_length` for most byte-oriented use cases — `bit_length` is useful when working with bit-level protocols.

example

SELECT octet_length(data) AS bytes, bit_length(data) AS bits FROM binary_records;

→Both measures side by side

octet_length

PG 8.0+→ integer

Returns the number of bytes in the binary string or text string (counting bytes, not characters).

DeveloperData EngSecurity

Signatures

octet_length ( bytea ) → integer

octet_length ( text ) → integer

Parameters

Parameter	Type	Description
bytes_or_text	bytea or text	Value to measure in bytes

Examples

sql

SELECT octet_length('\x123456'::bytea);

→3

sql

SELECT octet_length('hello'::text);

→5

Euro sign is 3 bytes in UTF-8

sql

SELECT octet_length('€'::text);

→3

sql

SELECT id, octet_length(payload) AS size_bytes FROM messages WHERE octet_length(payload) > 65535;

→Messages exceeding 64 KB payload limit

⚠Anti-Pattern

For multibyte text (UTF-8), `length(str)` counts characters while `octet_length(str)` counts bytes. Use `octet_length` to check if a value fits a byte-limited field or network buffer.

example

SELECT name, length(name) AS chars, octet_length(name) AS bytes FROM users WHERE octet_length(name) > 100;

→Names that exceed 100 bytes in UTF-8

btrim

PG 8.0+→ bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start and end of bytes.

DeveloperData EngSecurity

Signature

btrim ( bytes bytea, bytesremoved bytea ) → bytea

Parameters

Parameter	Type	Description
bytes	bytea	Binary string to trim
bytesremoved	bytea	Set of bytes to trim from both ends

Examples

sql

SELECT btrim('\x1234567890'::bytea, '\x9012'::bytea);

→\x345678

sql

SELECT btrim('\x00112200'::bytea, '\x00'::bytea);

→\x1122

sql

SELECT btrim('\xFF0A0BFF'::bytea, '\xFF'::bytea);

→\x0a0b

sql

SELECT btrim(raw_frame, '\x00FF'::bytea) AS stripped FROM sensor_data;

→Frame with null and 0xFF padding removed from both ends

⚠Anti-Pattern

Binary data from external systems often includes null byte `\x00` padding. Use `btrim(data, '\x00'::bytea)` to strip them from both ends before processing.

example

SELECT btrim(raw_data, '\x00'::bytea) AS clean_data FROM sensor_readings;

→Binary data with null padding removed

ltrim

PG 8.0+→ bytea

Removes the longest string containing only bytes from bytesremoved from the start (left) of bytes.

DeveloperData EngSecurity

Signature

ltrim ( bytes bytea, bytesremoved bytea ) → bytea

Parameters

Parameter	Type	Description
bytes	bytea	Binary string to trim
bytesremoved	bytea	Set of bytes to remove from the left

Examples

sql

SELECT ltrim('\x1234567890'::bytea, '\x9012'::bytea);

→\x34567890

sql

SELECT ltrim('\x00001234'::bytea, '\x00'::bytea);

→\x1234

sql

SELECT ltrim(raw_packet, '\x00'::bytea) AS no_leading_nulls FROM packets;

→Packets with leading null bytes stripped

sql

SELECT ltrim(encoded_val, '\x00'::bytea) AS compact FROM ber_fields;

→BER-encoded integer with leading zero bytes stripped

⚠Anti-Pattern

Some binary formats embed a known header. Use `ltrim` or `substring` to skip past it and access the payload portion of binary data.

example

SELECT ltrim(file_data, '\xFF\xD8'::bytea) AS jpeg_body FROM images;

→JPEG data with SOI marker removed

rtrim

PG 8.0+→ bytea

Removes the longest string containing only bytes from bytesremoved from the end (right) of bytes.

DeveloperData EngSecurity

Signature

rtrim ( bytes bytea, bytesremoved bytea ) → bytea

Parameters

Parameter	Type	Description
bytes	bytea	Binary string to trim
bytesremoved	bytea	Set of bytes to remove from the right

Examples

sql

SELECT rtrim('\x1234567890'::bytea, '\x9012'::bytea);

→\x12345678

sql

SELECT rtrim('\x12340000'::bytea, '\x00'::bytea);

→\x1234

sql

SELECT rtrim(padded_blob, '\xFF'::bytea) AS unpadded FROM fixed_records;

→Blob with trailing 0xFF padding removed

sql

SELECT rtrim(fixed_field, '\x20'::bytea) AS trimmed FROM char_fields;

→Fixed-width field with trailing space bytes (0x20) removed

⚠Anti-Pattern

Fixed-length binary fields often have trailing null or 0xFF padding bytes. Use `rtrim(data, '\x00'::bytea)` or `rtrim(data, '\xFF'::bytea)` to remove them.

example

SELECT rtrim(padded_field, '\x00'::bytea) AS unpadded FROM fixed_width_records;

→Binary field with trailing zeros removed

substring

PG 8.0+→ bytea

Extracts a substring from a binary string by byte position.

DeveloperData EngSecurity

Signature

substring ( bytes bytea [ FROM start integer ] [ FOR count integer ] ) → bytea

Parameters

Parameter	Type	Description
bytes	bytea	Binary string to extract from
start	integer	Starting byte position (1-based)
count	integer	Number of bytes to extract

Examples

sql

SELECT substring('\x1234567890'::bytea from 2 for 3);

→\x345678

sql

SELECT substring(file_header from 1 for 4) AS magic_bytes FROM files;

→First 4 bytes (magic number)

Omitting FOR returns bytes to end of string

sql

SELECT substring('\xDEADBEEF'::bytea from 3);

→\xbeef

sql

SELECT encode(substring(frame from 5 for 2), 'hex') AS frame_type FROM packets;

→2-byte type field from byte offset 5

⚠Anti-Pattern

Binary file formats (PNG, PDF, ZIP) have magic bytes at known offsets. Use `substring(data FROM offset FOR length)` to read and verify them without external tools.

example

SELECT id, encode(substring(data FROM 1 FOR 4), 'hex') AS magic FROM stored_files;

→Magic bytes: 89504e47=PNG, 25504446=PDF

overlay

PG 8.0+→ bytea

Replaces a portion of a binary string starting at a given byte position.

DeveloperData EngSecurity

Signature

overlay ( bytes bytea PLACING newsubstring bytea FROM start integer [ FOR count integer ] ) → bytea

Parameters

Parameter	Type	Description
bytes	bytea	Original binary string
newsubstring	bytea	Binary string to insert
start	integer	Starting byte position (1-based)
count	integer	Number of bytes to replace (defaults to length of newsubstring)

Examples

sql

SELECT overlay('\x1234567890'::bytea placing '\x002003'::bytea from 2 for 3);

→\x12002003090a

sql

SELECT overlay(template placing new_checksum from octet_length(template) - 1 for 2) AS patched FROM frames;

→Frame with last 2 bytes replaced by new checksum

Replace single byte at position 3

sql

SELECT overlay('\xAABBCCDDEE'::bytea placing '\xFF'::bytea from 3 for 1);

→\xaabbffddee

sql

SELECT overlay(record placing decode(lpad(to_hex(new_seq), 4, '0'), 'hex') from 1 for 2) FROM records;

→First 2 bytes updated with new sequence number

⚠Anti-Pattern

Use `overlay` to patch known-offset fields in binary data structures without decoding the entire record. Combine with `set_byte` for single-byte changes.

example

SELECT overlay(firmware placing new_header FROM 1 FOR 16) AS patched FROM firmware_images;

→Firmware with header bytes replaced

position

PG 8.0+→ integer

Returns the first starting byte index of the specified substring within the binary string, or zero if not present.

DeveloperData EngSecurity

Signature

position ( substring bytea IN bytes bytea ) → integer

Parameters

Parameter	Type	Description
substring	bytea	Binary string to search for
bytes	bytea	Binary string to search within

Examples

sql

SELECT position('\x5678'::bytea in '\x1234567890'::bytea);

→3

sql

SELECT position('\xFF'::bytea in data) AS ff_offset FROM binary_data;

→Position of first 0xFF byte

sql

SELECT position('\xDEAD'::bytea in '\xBEEFDEADCAFE'::bytea);

→3

sql

SELECT id FROM packets WHERE position('\x0D0A0D0A'::bytea IN raw_data) > 0;

→Packets containing HTTP double-CRLF separator

⚠Anti-Pattern

Use `position` to locate delimiters or magic sequences in binary data. A return value of 0 means not found — check with `> 0` rather than `IS NOT NULL`.

example

SELECT id, position('\x0D0A'::bytea IN raw_response) AS crlf_pos FROM http_captures;

→Position of CRLF (\r\n) line ending

get_byte

PG 7.4+→ integer

Returns the value of the n-th byte in the binary string (0-based indexing). Returns an integer from 0 to 255.

DeveloperData EngSecurity

Signature

get_byte ( bytes bytea, n integer ) → integer

Parameters

Parameter	Type	Description
bytes	bytea	Binary string
n	integer	0-based byte index

Examples

0x12 = 18 decimal

sql

SELECT get_byte('\x1234567890'::bytea, 0);

→18

sql

SELECT get_byte('\xFF00'::bytea, 1);

→0

sql

SELECT get_byte(data, 0) AS first_byte FROM packets;

→First byte value (0-255)

sql

SELECT get_byte(header, 0) AS version, get_byte(header, 1) AS msg_type FROM frames;

→Parsed header fields from binary frame

⚠Anti-Pattern

`get_byte` uses 0-based indexing. Byte 0 is the first byte. `substring` uses 1-based FROM. Don't mix them up when parsing binary frames.

example

SELECT get_byte(header, 0) AS version, get_byte(header, 1) AS flags FROM packets;

→Individual byte field values from binary header

set_byte

PG 7.4+→ bytea

Sets the value of the n-th byte in the binary string to newvalue (0-255). Returns the modified bytea.

DeveloperData EngSecurity

Signature

set_byte ( bytes bytea, n integer, newvalue integer ) → bytea

Parameters

Parameter	Type	Description
bytes	bytea	Binary string to modify
n	integer	0-based byte index to set
newvalue	integer	New byte value (0–255)

Examples

sql

SELECT set_byte('\x1234567890'::bytea, 2, 255);

→\x1234ff7890

sql

SELECT set_byte(data, 0, 0) FROM packets;

→Sets first byte to 0x00

sql

SELECT set_byte(set_byte(header, 0, 2), 1, 0) AS patched_header FROM frames;

→Version set to 2, second byte zeroed

sql

UPDATE packets SET data = set_byte(data, 3, get_byte(data, 3) | 128) WHERE id = $1;

→Sets the high bit of byte 3 in-place

⚠Anti-Pattern

To flip a specific bit in byte n: `set_byte(data, n, get_byte(data, n) | (1 << bit_pos))` to set it, or `& ~(1 << bit_pos)` to clear it.

example

SELECT set_byte(flags, 0, get_byte(flags, 0) | 4) AS flags_with_bit2_set FROM config;

→Byte with bit 2 set

encode

PG 7.4+→ text

Encodes binary data as text using the specified encoding: 'base64', 'hex', or 'escape'.

DeveloperData EngSecurity

Signature

encode ( bytes bytea, format text ) → text

Parameters

Parameter	Type	Description
bytes	bytea	Binary data to encode
format	text	'base64', 'hex', or 'escape'

Examples

sql

SELECT encode('\x48656c6c6f'::bytea, 'hex');

→48656c6c6f

sql

SELECT encode('Hello'::bytea, 'base64');

→SGVsbG8=

sql

SELECT encode(digest_value, 'hex') AS sha256_hex FROM hashes;

→Hexadecimal hash string

sql

SELECT replace(encode(avatar, 'base64'), E'\n', '') AS compact_b64 FROM user_profiles;

→Base64 without embedded newlines for JSON embedding

⚠Anti-Pattern

Use `encode(data, 'hex')` for human-readable hex output. Use `encode(data, 'base64')` for compact transmission (web APIs, JSON). PostgreSQL's base64 output includes newlines every 76 chars — remove them with `replace(encode(b, 'base64'), E'\n', '')`.

example

SELECT replace(encode(image_data, 'base64'), E'\n', '') AS base64_image FROM user_avatars;

→Compact base64 without newlines

decode

PG 7.4+→ bytea

Decodes a text string in the specified encoding ('base64', 'hex', or 'escape') into binary data.

DeveloperData EngSecurity

Signature

decode ( string text, format text ) → bytea

Parameters

Parameter	Type	Description
string	text	Encoded text to decode
format	text	'base64', 'hex', or 'escape'

Examples

sql

SELECT decode('SGVsbG8=', 'base64');

→\x48656c6c6f

sql

SELECT decode('deadbeef', 'hex');

→\xdeadbeef

sql

SELECT convert_from(decode(b64_col, 'base64'), 'UTF8') AS text FROM encoded_text;

→Decoded UTF-8 text from base64

sql

INSERT INTO blobs (data) VALUES (decode(trim($1), 'base64'));

→Safely inserts base64-encoded upload after trimming whitespace

⚠Anti-Pattern

When receiving binary content as base64 strings (common in REST APIs), use `decode(base64_str, 'base64')` to convert to bytea for storage or further processing.

example

INSERT INTO files (name, data) VALUES ($1, decode($2, 'base64'));

→Stores binary file from base64-encoded input

sha256

PG 11+→ bytea

Computes the SHA-256 hash of the binary string, returning a 32-byte (256-bit) bytea.

DeveloperData EngSecurity

Signature

sha256 ( bytea ) → bytea

Parameters

Parameter	Type	Description
bytes	bytea	Binary data to hash

Examples

sql

SELECT encode(sha256('hello'::bytea), 'hex');

→2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824

sql

SELECT encode(sha256(file_data), 'hex') AS hash FROM stored_files;

→SHA-256 hash of each file

sql

SELECT id FROM documents WHERE sha256(content) = decode($1, 'hex');

→Find document matching a known SHA-256 hash

sql

SELECT id, encode(sha256(payload), 'hex') AS integrity_hash FROM messages ORDER BY id;

→Per-row integrity fingerprint for audit log

⚠Anti-Pattern

Store SHA-256 hashes as `bytea` (32 bytes, compact) rather than `text`. Use `encode(sha256(data), 'hex')` only for display. Index comparisons on `bytea` columns are fast.

example

SELECT id FROM documents WHERE sha256(content) = decode($1, 'hex');

→Find document by SHA-256 content hash

md5 (bytea)

PG 8.0+→ text

Computes the MD5 hash of binary data, returning a 32-character lowercase hexadecimal string.

DeveloperData EngSecurity

Signature

md5 ( bytea ) → text

Parameters

Parameter	Type	Description
bytes	bytea	Binary data to hash

Examples

sql

SELECT md5('\x48656c6c6f'::bytea);

→8b1a9953c4611296a827abf8c47804d7

sql

SELECT md5(file_data) FROM stored_files;

→MD5 hex digest of file content

sql

SELECT id, md5(content) AS etag FROM assets WHERE md5(content) != stored_etag;

→Assets whose content has changed since etag was recorded

sql

SELECT md5(large_blob) = expected_md5 AS integrity_ok FROM uploads;

→Quick integrity check for non-security use case

⚠Anti-Pattern

MD5 is cryptographically broken — don't use it for passwords or security-sensitive hashes. Use SHA-256 (`sha256`) or pgcrypto's `digest(data, 'sha256')`. MD5 is still fine for non-security checksums (data deduplication, ETags).

example

-- For ETags/deduplication (non-security):
SELECT md5(data) AS etag FROM content;
-- For security, use sha256 instead

→Fast non-cryptographic checksum

PostgreSQL Binary String (bytea) Functions

Jump to function

What are PostgreSQL Binary String (bytea) Functions?

|| (bytea concatenation)

Signature

Parameters

Examples

bit_length

Signature

Parameters

Examples

octet_length

Signatures

Parameters

Examples

btrim

Signature

Parameters

Examples

ltrim

Signature

Parameters

Examples

rtrim

Signature

Parameters

Examples

substring

Signature

Parameters

Examples

overlay

Signature

Parameters

Examples

position

Signature

Parameters

Examples

get_byte

Signature

Parameters

Examples

set_byte

Signature

Parameters

Examples

encode

Signature

Parameters

Examples

decode

Signature

Parameters

Examples

sha256

Signature

Parameters

Examples

md5 (bytea)

Signature

Parameters

Examples

Related PostgreSQL Categories