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?
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).
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.
Decoding
SELECT CONVERT_FROM(DECODE(field, 'BASE64'), 'UTF-8') FROM table;
Encoding
SELECT ENCODE(CONVERT_TO(field, 'UTF-8'), 'base64') FROM table;
I always use my own method
create function owndecode(password character varying) returns bytea
language plpgsql
as $$
declare
_length int;
begin
_length := char_length(password);
if (_length in(3, 5, 7, 9, 11, 13, 15)) then
return decode(password || '=', 'base64');
elseif (_length in (6, 10, 14)) then
return decode(password || '==', 'base64');
elseif (_length in(8, 12)) then
return decode(password, 'base64');
else
return char_length(password)::varchar;
end if;
end;
$$;
set bytea_output to 'escape';(default on earlier versions) it will return wanted result