2

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.

2 Answers 2

5

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.