如何正确将PostgreSQL的bytea类型数据解密为文本？

当你使用pgp_sym_decrypt_bytea解密存储在bytea字段中的加密密码时，返回的是十六进制格式的bytea数据（如\x70617373776f726431），可以通过以下方法将其转换为可读的文本字符串：

方法一：使用convert_from()函数（推荐） #

convert_from()可以将二进制数据（bytea）按照指定编码转换为文本，这是最可靠的方式，能确保编码正确避免乱码。针对你的场景，执行以下SQL：

SELECT convert_from(pgp_sym_decrypt_bytea(password, 'key1'), 'UTF8') FROM user_passwords WHERE username='user1';

执行后会直接输出原始文本密码password1。

方法二：直接转换为text类型 #

也可以通过类型转换操作符::text将bytea转为文本，适用于明确原始数据为UTF8编码的场景：

SELECT pgp_sym_decrypt_bytea(password, 'key1')::text FROM user_passwords WHERE username='user1';

补充说明 #

PostgreSQL官方文档禁止用pgp_sym_decrypt解密bytea类型数据，是因为如果加密的原始数据包含非文本二进制内容，直接转文本会产生无效字符。而pgp_sym_decrypt_bytea返回原始二进制数据，再通过convert_from或类型转换转文本，能安全处理文本类加密数据（如密码）。

内容的提问来源于stack exchange，提问作者ani