PostgreSQL8与12版本bytea存储字符串行为差异及兼容方案问询

1. 两个版本行为差异的根本原因 #

两个版本的bytea类型存储逻辑完全一致，差异只出现在查询时的默认输出格式上：

• PostgreSQL 8.x全版本默认使用escape作为bytea的输出格式：该格式下可打印的ASCII字符会直接以原始字符展示，仅不可打印字符会用转义序列表示，所以你查询得到的是可读的hello world。
• 从PostgreSQL 9.0版本开始，官方将bytea的默认输出格式调整为hex：该格式会把所有bytea内容统一编码为\x开头的十六进制字符串展示，所以PG12中查询会返回\x68656c6c6f20776f726c64。

2. PG12兼容PG8 bytea输出行为的配置方法 #

你可以根据使用场景选择以下任意一种配置方式修改bytea_output参数：

• 仅当前会话临时生效：执行SQL命令 SET bytea_output = 'escape'; 即可，退出会话后配置自动失效。
• 针对指定数据库用户永久生效：执行SQL命令 ALTER ROLE <你的用户名> SET bytea_output = 'escape';，后续该用户新建的所有连接都会默认使用escape格式输出bytea内容。
• 全局所有用户永久生效：修改PostgreSQL配置文件postgresql.conf，找到bytea_output参数项，将值改为escape，保存配置后重启PostgreSQL服务即可。

配置完成后可以执行SHOW bytea_output;命令验证当前生效的参数值，确认调整成功。

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