how to convert base64 string to bytea in postgresql8.2

Question

I need to convert base64 string to bytea type. But when I Executed SQL statements by the pgAdminIII：

select decode("ygAAA", 'base64');

I got the following error message:

ERROR:  syntax error at or near ")"
LINE 1: select decode('ygAAA', 'base64');
                                                              ^
********** 错误 **********

ERROR: syntax error at or near ")"
SQL 状态: 42601
字符:59

My postgresql's version is 8.2.15. And I could use encode function. I googled it, but didn't find the solution. Can somebody help me? TKS!

Answer 1

Try it with single quotes instead of double quotes. Also base 64 strings turn groups of 4 characters into 3 bytes (24 bits in 3 bytes are spread across the lower 6 bits of 4 characters.) So your base64 string is invalid.

This works:

select decode('ygAA', 'base64');

Hope this helps,

Adam.