CONVERT_FROM

Decode binary data (BYTEA) into text using a specified source character encoding.

Category: encodingReturns: STRINGDialect: PostgreSql

Syntax

CONVERT_FROM(data, src_encoding)

Description

## Overview Decodes a BYTEA value using the specified source character encoding and returns the result as a text string in the database's internal encoding (typically UTF-8). Use this function when binary data arrives in a legacy or non-default encoding and must be decoded for display or string operations. The function is the inverse of CONVERT_TO: pairing CONVERT_FROM(CONVERT_TO(s, enc), enc) yields the original string when all characters are representable in the target encoding. ## Behavior - Returns NULL if either argument is NULL. - Raises an error if the input bytes contain invalid sequences for the declared source encoding. - Encoding name is case-insensitive; aliases such as 'UTF-8' and 'utf8' are accepted. - Decoding is strict: invalid input is not silently substituted. - Return type is STRING (TEXT). - Performance is proportional to input length; no additional allocation beyond the output string. ## Compatibility - Encoding names follow the iconv / PostgreSQL encoding catalog. - Decoded output uses the session client encoding.

Parameters

Name	Type	Description
`data`		Specifies the binary data (BYTEA) to decode. Returns NULL for NULL input.
`src_encoding`		Specifies the character encoding of the input bytes. Accepts standard encoding names including 'UTF8', 'LATIN1', 'WIN1252', 'SQL_ASCII'. The encoding name is case-insensitive.

Examples

-- Decode UTF-8 bytes to text
SELECT CONVERT_FROM('\x48656c6c6f'::BYTEA, 'UTF8') AS t;

-- Decode LATIN1 bytes (e.g. 'Helló' in LATIN1)
SELECT CONVERT_FROM('\x48e96c6c6f'::BYTEA, 'LATIN1') AS t;

-- NULL input returns NULL
SELECT CONVERT_FROM(NULL, 'UTF8') AS t;

-- Round-trip through a non-default encoding
SELECT CONVERT_FROM(CONVERT_TO('Hello', 'LATIN1'), 'LATIN1') AS t;

-- Realistic: decode payloads stored as BYTEA in an ingestion table
SELECT message_id, CONVERT_FROM(raw_body, 'UTF8') AS body_text
FROM messaging.inbound.raw_events
WHERE received_date = DATE '2026-04-19';

Pitfalls

Invalid byte sequences for the declared encoding raise an error; wrap in a TRY or pre-validate when data quality is mixed.
The encoding name is case-insensitive, but spelling must match one of the supported aliases (for example 'UTF8', 'UTF-8', 'LATIN1', 'ISO-8859-1').
SQL_ASCII does not validate input and may yield garbled text if bytes are not really ASCII; use with care.
Bytes that are valid in multiple encodings but differ in meaning (for example LATIN1 vs WIN1252) cannot be disambiguated from the data alone; pass the correct source encoding explicitly.
Converting from a multi-byte encoding to SQL_ASCII can silently drop high-bit characters; test round-trips end-to-end.