postgresql - 如何将 PostgreSQL 转义 bytea 转换为十六进制 bytea？

Question

我得到了 answer检查 PostgreSQL 文本列中的某个 BOM。我真正喜欢做的是拥有更通用的东西，即像

select decode(replace(textColumn, '\\', '\\\\'), 'escape') from tableXY;

UTF8 BOM 的结果是:

\357\273\277

其中是八进制的bytea，可以在pgadmin中切换bytea的输出进行转换:

update pg_settings set setting = 'hex' WHERE name = 'bytea_output';
select '\357\273\277'::bytea

结果是:

\xefbbbf

我想要的是这个结果作为一个查询，例如

update pg_settings set setting = 'hex' WHERE name = 'bytea_output';
select decode(replace(textColumn, '\\', '\\\\'), 'escape') from tableXY;

但这行不通。结果为空，可能是因为解码无法处理十六进制输出。

Answer 1

如果最终目的是获取构成textColumn中字符串的所有字节的十六进制表示，这可以通过:

SELECT encode(convert_to(textColumn, 'UTF-8'), 'hex') from tableXY;

它不依赖于bytea_output。顺便说一句，此设置仅在查询的最后阶段起作用，此时结果列的类型为 bytea 并且必须以文本格式返回给客户端(这是最常见的情况，以及 pgAdmin 的作用)。这是表示的问题，表示的实际值(字节序列)是相同的。

在上面的查询中，结果是 text 类型，所以这无论如何都是无关紧要的。

我认为您使用 decode(..., 'escape') 的查询无法正常工作，因为参数应该以 escape 格式进行编码，并且它是不是，根据评论，它是普通的 xml 字符串。