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!

your error does not correlate to your query – Vao Tsun Apr 27 '17 at 07:08 — Vao Tsun, Apr 27 '17 at 07:08

score 4 · Answer 1 · answered Apr 27 '17 at 10:29

4

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.

answered Apr 27 '17 at 10:29

Adam Benson

7,480
4
22
45

I tried it，but it didn't work. I asked the EMC^2's engineer， he said the decode function doesn't work in Greenplum. Sorry，I didn't describe it clearly，I tried it in Greenplum database 4.3.8.1 build 1.Still thank you！ – yelan_fn Jun 09 '17 at 02:50

how to convert base64 string to bytea in postgresql8.2

1 Answers1