In Postgresql how to get base64 decoded values as string instead of hex representation?

user_7795881 0
Viewed 26939

In Python I encoded one string,

>>> base64.b64encode('1111')
'MTExMQ=='

Now when I try to decode it in Postgresql I got Hex values instead of the original string.

=# select decode('MTExMQ==', 'base64');

   decode   
------------
 \x31313131

How can I get the original string without any change in Postgresql?

base64 postgresql
2 Answers

You can use convert_from to turn binary data into a varchar:

select convert_from(decode('MTExMQ==', 'base64'), 'UTF8')

But note that this can be lossy (or fail) if the input is really binary data (and not some text), such as an image file.

There should be no need to store Base64-encoded data in Postgresql. You can use one of the binary column types and store the data directly (and more compactly).

user_149550

decode() returns a byte array (bytea) not a "hex string".

The format you see is the default textual representation of a bytea which is e.g. used by psql when displaying bytea values. Other SQL clients might display that differently.

To convert a byte array to a readable string, you need to use encode()

select encode('\x31313131', 'base64');

If you change bytea_output to escape and then cast the result to text you can achieve the same thing:

set bytea_output to 'escape';
select decode('MTExMQ==', 'base64')::text;

If you are using psql you don't need the cast.

user_3303150
Related