CODEWITHSUNDEEP
postgresql
fyodorfranz
fyodorfranz

Reputation: 486

Converting bytea back to varchar

In Postgres when I want to save a varchar to a bytea column, this is made easy by an implicit conversion. So I can simply execute

UPDATE my_table SET my_bytea_col = 'This varchar will be converted' WHERE id = 1;

I use this all the time. However, I would like to occasionally see the contents of this column as a varchar. IDEs will handle this for you, but I would prefer in my use case to return the results with the bytea converted back to a varchar.

Of course I've tried something like this, among more complex options:

select my_bytea_col::VARCHAR from my_table WHERE id = 1

This, however, doesn't return my original readable text. How else can I convert my bytea back to the original varchar after postgres's implicit conversion in updates and inserts like the one above?

Upvotes: 2

Views: 6397

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248030

If the string encoding is UTF-8, you could use

SELECT convert_from(my_bytea_col, 'UTF8')
FROM my_table
WHERE id = 1;

If the encoding is different, you need to supply the appropriate second argument (e.g. LATIN1) to convert_from.

May I remark that I consider it not a good idea to store text strings as bytea?

Upvotes: 4

Related Questions