Convert BYTEA to String in PostgreSQL: How-To Guide

Updated

PostgreSQL elephant logo

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.

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') or encode(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.

Photo of author
Sudhir P. founded HeatWare.com in 1999 and has built and operated it full-stack ever since; it is now used by more than 88,000 people. He writes here about the PostgreSQL, MySQL, Linux and DevOps work that keeps it running. Articles are rewritten only after the commands in them have actually been run, and the verification scripts are published alongside them so anyone can check the claims. Reach him at blog@heatware.net.