PG_ENCODE

Encode binary data (BYTEA) as text using base64, hex, or escape format.

Category: encodingReturns: STRINGDialect: PostgreSql

Syntax

ENCODE(data, format)

Description

## Overview Converts binary data (BYTEA) into a text representation using one of three formats: - 'hex': lowercase hexadecimal, two characters per byte, no separators. - 'base64': standard Base64 per RFC 4648 with padding and no line wrapping. - 'escape': backslash-octal escape format where printable ASCII bytes pass through and other bytes are encoded as \nnn octal. Use ENCODE when you need a text representation of bytes for transport over text channels, human inspection, or storage in a text column. Pair with DECODE for round-trip conversion. ## Behavior - Returns NULL for NULL input. - Hex output is lowercase and contains no separators or prefix. - Base64 output is padded with '=' to a multiple of 4 characters; no line wrapping is applied. - Escape output emits printable ASCII as-is and bytes outside 0x20-0x7E as \nnn octal escapes. - Format name is case-insensitive. - Result type is STRING. ## Compatibility - Base64 output conforms to RFC 4648 (standard alphabet, padded). - Hex output matches RFC 4648 Base16 except always lowercase. - 'escape' is a database-specific format historically used for BYTEA text output.

Parameters

Name	Type	Description
`data`		Specifies the binary data (BYTEA) to encode as text. Returns NULL for NULL input.
`format`		Specifies the target encoding format. Valid values: 'base64', 'hex', 'escape'. The format name is case-insensitive.

Examples

-- Encode as hex (lowercase by default)
SELECT ENCODE('Hello'::BYTEA, 'hex') AS hex;

-- Encode as base64 (padded, no line wrap)
SELECT ENCODE('Hello'::BYTEA, 'base64') AS b64;

-- Encode as escape format
SELECT ENCODE('Hello'::BYTEA, 'escape') AS esc;

-- Round-trip with DECODE
SELECT ENCODE(DECODE('SGVsbG8=', 'base64'), 'hex') AS hex_back;

-- Realistic: emit signatures as hex for log lines
SELECT event_id, ENCODE(signature, 'hex') AS signature_hex
FROM security.audit.signed_events
WHERE created_date = DATE '2026-04-19';

Pitfalls

Hex output is lowercase. Consumers that expect uppercase hex must UPPER() the result.
Base64 output is the standard alphabet with '+' and '/'. For URL-safe output replace '+' with '-' and '/' with '_' in post-processing.
No line wrapping is applied, so very long base64 strings may exceed line-length expectations of downstream text tools (editors, email). Add explicit wrapping if needed.
'escape' is lossless but ambiguous if downstream consumers do not know the format; default to 'hex' or 'base64' for cross-system transport.
ENCODE produces TEXT; LENGTH() returns character count, which for base64 is roughly 4/3 of the input byte count (plus padding).