12

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.

Improve this question

2 Answers 2

Reset to default
17

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.

Improve this answer
4

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.