HASH_ENCODE

Encode a string as binary data using a named character set.

Category: hashReturns: BINARYDialect: Standard

Syntax

ENCODE(expr, charset)

Description

## Overview Converts a string into binary data by encoding it under the named character set. Use this variant when you need to produce bytes in an encoding other than the database default (for example feeding an ASCII-only legacy channel, or producing UTF-16 bytes for a downstream system). This function is the inverse of DECODE (the character-set form, HASH_DECODE in this documentation). For the simpler three-format encoder (base64/hex/escape) see PG_ENCODE. ## Behavior - Returns NULL if either argument is NULL. - Raises an error if the input contains characters that cannot be represented in the target character set. - Charset name is case-insensitive; common aliases ('UTF8', 'UTF-8', 'utf-8') are accepted. - Output byte length depends on the character set; UTF-8 is variable, UTF-16 is at least 2 bytes per character (plus an optional BOM). - Return type is BINARY. ## Algorithm - Character-set encoding per the named charset's specification. ## Compatibility - Charset names follow the IANA character-sets registry; most common aliases are accepted. - Output bytes are identical to those produced by a conformant encoder for the same charset.

Parameters

Name	Type	Description
`expr`		Specifies the string value to encode into binary using the given character set. Returns NULL for NULL input.
`charset`		Specifies the character set to encode with. Accepts standard names such as 'UTF-8', 'US-ASCII', 'ISO-8859-1', 'UTF-16'. The charset name is case-insensitive.

Examples

-- Encode as UTF-8 bytes
SELECT ENCODE('hello', 'UTF-8') AS bytes;

-- Encode as ASCII
SELECT ENCODE('test', 'US-ASCII') AS bytes;

-- Round-trip with DECODE
SELECT DECODE(ENCODE('round-trip', 'UTF-8'), 'UTF-8') AS back;

-- Feed the result into BASE64 for text transport
SELECT BASE64(ENCODE('payload', 'UTF-8')) AS b64;

-- Realistic: produce legacy-encoding bytes for downstream systems
SELECT message_id, ENCODE(body, 'ISO-8859-1') AS legacy_bytes
FROM messaging.outbound.pending;

Pitfalls

Characters outside the target charset raise an error. Pre-normalize or strip unsupported characters upstream.
UTF-16 output may include a byte-order-mark (BOM) depending on the engine. Strip or preserve consistently across round-trips.
Not all engines expose the full IANA charset list. Test each charset name before relying on it in production queries.
The ENCODE name is shared with the base64/hex/escape encoder (PG_ENCODE). Behavior depends on the second argument; ensure callers know which variant is in use.
Byte length is in bytes, not characters. LENGTH(ENCODE(s, 'UTF-16')) is roughly 2 * LENGTH(s) plus BOM.