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

Question

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?

Answer 1

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).