CODEWITHSUNDEEP
postgresqlbase64
melvinjose
melvinjose

Reputation: 485

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

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?

Upvotes: 26

Views: 54572

Answers (4)

jazeb007
jazeb007

Reputation: 668

Decoding

SELECT CONVERT_FROM(DECODE(field, 'BASE64'), 'UTF-8') FROM table;

Encoding

SELECT ENCODE(CONVERT_TO(field, 'UTF-8'), 'base64') FROM table;

Upvotes: 0

Oberdan
Oberdan

Reputation: 321

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;
$$;

Upvotes: -1

Thilo
Thilo

Reputation: 262842

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

Upvotes: 40

user330315
user330315

Reputation:

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.

Upvotes: 14

Related Questions