PG_DECODE

Decode a text string into binary data using base64, hex, or escape format.

Category: encodingReturns: BINARYDialect: PostgreSql

Syntax

DECODE(str, format)

Description

## Overview Converts an encoded text string back into binary data (BYTEA) using the specified format. Three formats are supported: - 'hex': pairs of hexadecimal digits (0-9, A-F, case-insensitive). - 'base64': standard Base64 per RFC 4648 with padding and no line-wrapping assumption. - 'escape': backslash-octal escape format used by the database for text representation of BYTEA. Use DECODE when you receive encoded binary as text (over APIs, in logs, or from text columns) and need raw bytes for hashing, comparison, or re-encoding. ## Behavior - Returns NULL for NULL input. - Raises an error if the input does not conform to the declared format. - For 'base64', strict decoding is applied: invalid characters or bad padding raise an error. - For 'hex', the string length must be even and contain only hex digits. - For 'escape', printable ASCII passes through unchanged; \nnn octal escapes are decoded. - Format name is case-insensitive. ## Compatibility - Base64 decoding follows RFC 4648. - Hex decoding is case-insensitive input with no prefix ('0x' not accepted). - 'escape' is a database-specific format used historically for BYTEA text output.

Parameters

Name	Type	Description
`str`		Specifies the encoded text string to decode into binary data. Returns NULL for NULL input.
`format`		Specifies the encoding format of the input. Valid values: 'base64', 'hex', 'escape'. The format name is case-insensitive.

Examples

-- Decode hex
SELECT DECODE('48656c6c6f', 'hex') AS bytes;

-- Decode base64
SELECT DECODE('SGVsbG8=', 'base64') AS bytes;

-- Decode escape-format text
SELECT DECODE('Hello', 'escape') AS bytes;

-- Round-trip with ENCODE
SELECT DECODE(ENCODE('test data'::BYTEA, 'base64'), 'base64') AS back;

-- Realistic: decode base64 payloads stored as text
SELECT attachment_id, DECODE(payload_b64, 'base64') AS attachment_bytes
FROM messaging.inbound.attachments
WHERE received_date = DATE '2026-04-19';

Pitfalls

Base64 decoding is strict and requires proper padding. Stripped padding or URL-safe Base64 (-/ replaced with _) raises an error; re-pad and re-map before decoding.
Hex input must have an even character count; an odd length raises an error. Do not accept a '0x' prefix.
'escape' is not the same as 'hex' or 'base64'. Passing raw printable text with backslash sequences as 'hex' will fail.
Base64 with embedded line breaks (MIME-style wrapping at 76 characters) is not accepted by default; strip whitespace before decoding.
The DECODE name is also used in other SQL dialects for Oracle-style conditional evaluation. In this context it is strictly binary decoding; it does not evaluate conditions.