How to convert a bytea column to text?

Question

how to convert a bytea column to text in PostgreSQL so that I can read the column properly in PGADMIN?

I have the following SQL query in the PGADMIN's query editor:

SELECT event_type, created_at, encode(metadata::bytea, 'escape') 
FROM public.events 
ORDER BY created_at DESC
LIMIT 100

However, it produces an encoded column with each records more or less ressembling the following output:

\203t\00000some_textd\000some_other_textd\0000

How can I get rid of this encoded, so that I only see the original value of the column, in the text format:

some_text some_data

What I have also tried:

SELECT event_id, event_type, created_at, decode((encode(metadata, 'escape')::text), 'escape')
FROM public.events
ORDER BY created_at DESC
LIMIT 100

But in the above case, the query returns a decode column of type bytea and I only see the field [binary data] for each record of the column.

I have also tried the first two answers mentioned here without success and can't properly translate the last answer to my query.

score 17 · Accepted Answer · answered Nov 25 '20 at 19:29

I finally found out how to display a bytea column as a text! I can use the function convert_from like so:

SELECT event_type, convert_from(metadata, 'UTF8') as metadata
FROM public.events 
ORDER BY created_at DESC
LIMIT 100

Then I'll have the metadata column in a human-readable format.

score 4 · Answer 2 · answered Aug 02 '20 at 12:33

How can I get rid of this encoded, so that I only see the original value of the column, in the text format

Bytes have 256 possible values, when there are only about 95 visualizable ASCII characters, (the range [32:126]), so the mapping between ASCII text and binary representation cannot be one-to-one.

The format produced by encode(metadata::bytea, 'escape') is just one possible representation, where some bytes are represented by sequences of characters. Others common representations are hexadecimal (outputs each byte as 2 characters) and base64 (outputs each 3 bytes as 4 characters).

If you don't want these and have a specific idea on how your metadata column should be output as text, then you can always provide a user function instead of encode. Right now your question does not specify what output you want to see, only that it's not the escape format.

How to convert a bytea column to text?

2 Answers2