The following bug has been logged online:
Bug reference: 5854
Logged by: Chris R.
Email address: chricki(at)gmx(dot)net
PostgreSQL version: 9.02
Operating system: CentOS 5.5
Description: base64 decode returns bytea and no text
Details:
There is a break in how pg9.0 handles decoding base64 encoded data.
With PostgreSQL 8.4:
select decode(encode('abc', 'base64'), 'base64');
decode
----------
\x616263
With PostgreSQL 9.0:
select decode(encode('abc', 'base64'), 'base64');
decode
----------
\x616263
To get the old result, convert_from helps out:
select convert_from(decode(encode('abc', 'base64'), 'base64'), 'UTF8');
Still, shouldn't this be consistent with 8.x and 9.x?