Sample Header Ad - 728x90

How to convert a bytea column to text?

12 votes
2 answers
43366 views
**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.
Asked by ryanzidago (395 rep)
Aug 1, 2020, 06:50 PM
Last activity: Nov 25, 2020, 07:29 PM