Use convert_from(column, 'UTF8'). It is the only one of the three
common answers that returns the original string — the
postgresql.org
documentation on binary string functions defines it as converting a binary string representing
text in the source encoding to text in the database encoding:
SELECT convert_from(body, 'UTF8') FROM documents;
encode(body, 'escape') mangles every non-ASCII character into octal, and
body::text gives you a hex dump, not a string. Both look correct on ASCII test data and
break on the first accented character or emoji.
Table of Contents
The three approaches, side by side
A table holding two text values encoded as UTF-8 bytes, plus four bytes of genuine binary:
CREATE TABLE documents (id serial primary key, label text, body bytea);
INSERT INTO documents (label, body) VALUES
('ascii', convert_to('hello world', 'UTF8')),
('accents', convert_to('café ☕', 'UTF8')),
('binary', 'xdeadbeef'::bytea);
Now run all three conversions on the two text rows:
SELECT label,
convert_from(body, 'UTF8') AS convert_from,
encode(body, 'escape') AS encode_escape,
body::text AS cast_to_text
FROM documents WHERE label <> 'binary' ORDER BY id;
label | convert_from | encode_escape | cast_to_text
---------+--------------+--------------------------+--------------------------
ascii | hello world | hello world | x68656c6c6f20776f726c64
accents | café ☕ | caf303251 342230225 | x636166c3a920e29895
(2 rows)
On the ASCII row all three look fine, which is exactly why the wrong function survives code
review. On the row with an accent and an emoji, only convert_from is right.
| Expression | Returns | Use it when |
|---|---|---|
convert_from(b, 'UTF8') |
The decoded string | The bytes are text. This is the default choice. |
encode(b, 'escape') |
ASCII kept, every other byte as nnn octal |
You want a printable, single-line representation of arbitrary bytes — a debugging view, not a string. |
encode(b, 'hex') |
68656c6c6f... |
Checksums, comparing to hashes, passing to another system. |
encode(b, 'base64') |
aGVsbG8gd29ybGQ= |
Embedding binary in JSON, email, or an API response. |
b::text |
The literal x hex form |
Never, if you wanted the string. |
That base64 row matters more once the payload lands back in Postgres: if you’re storing it in a jsonb column rather than handing it straight to an API, jsonb vs json is its own measured trade-off — containment and GIN indexing against byte-for-byte fidelity, the same question as the base64 encoding above one level up.
Why ::text gives you hex
Casting bytea to text produces bytea’s display format, and that
format is controlled by the bytea_output setting — which, per the
postgresql.org documentation
on binary data types, has defaulted to
hex since PostgreSQL 9.0, not escape:
SHOW bytea_output;
bytea_output
--------------
hex
SELECT 'hello world'::bytea;
bytea
--------------------------
x68656c6c6f20776f726c64
You can flip it, but it changes only how values are printed and it does not fix anything:
SET bytea_output = 'escape';
SELECT 'hello world'::bytea;
bytea
-------------
hello world
SELECT 'café ☕'::bytea;
bytea
--------------------------
caf303251 342230225
Same octal mangling as encode(…, 'escape'), because it is the same conversion.
Leave bytea_output alone and pick the right function instead.
When the bytes are not valid UTF-8
convert_from validates. Feed it real binary and it raises rather than returning
garbage:
SELECT convert_from(body, 'UTF8') FROM documents WHERE label = 'binary';
ERROR: invalid byte sequence for encoding "UTF8": 0xbe
That error is a feature — it tells you the column is not text, or is not the encoding you assumed. Two ways out, depending on which is true:
- Wrong encoding assumed. Name the real one:
convert_from(body, 'LATIN1'),'WIN1252','SJIS'. Single-byte encodings like LATIN1 map every possible byte, so they never raise — which also means they will happily return mojibake (café) if the guess is wrong. Silence is not confirmation. - Genuinely binary. Stop trying to make it a string. Use
encode(body, 'base64')orencode(body, 'hex').
Going the other way
convert_to is the exact inverse of convert_from, and
decode is the inverse of encode:
SELECT convert_to('café ☕', 'UTF8') = body AS round_trips
FROM documents WHERE label = 'accents';
round_trips
-------------
t
SELECT decode('68656c6c6f', 'hex');
decode
--------------
x68656c6c6f
One more trap: length
length() on a bytea counts bytes; on the decoded text it counts
characters. They differ the moment you leave ASCII:
SELECT length(body) AS bytes, length(convert_from(body,'UTF8')) AS chars
FROM documents WHERE label = 'accents';
bytes | chars
-------+-------
9 | 6
If you are enforcing a limit, decide which one you actually mean.
Verified 2026-08-09 on PostgreSQL 17.10 (aarch64-apple-darwin25.6.0), server and client encoding UTF8. Every command above was executed and the output pasted unedited, including the error. Reproduce all of it with this script, which builds a throwaway cluster in a temp directory, checks all 16 claims, and deletes it afterwards.