PostgreSQL中如何正确插入bytea[]数组并实现匹配查询？

问题场景 #

创建表结构：

create table my_table
(
    f1          bytea        not null,
    f2          bytea[]      not null,
);

使用如下语句插入十六进制数据：

INSERT INTO my_table (f1,f2) 
VALUES (
  E'\\xF7C26945C70B646321D89202DE7FDCAB1A49833A26C68027C228437AE04FB2A8',
  '{
    0xA6697E974E6A320F454390BE03F74955E8978F1A6971EA6730542E37B66179BC,
    0x4B52414B00000000000000000000000000000000000000000000000000000000
   }'
);

查询f1时可以正常匹配：

SELECT * FROM my_table 
WHERE f1 = '\xF7C26945C70B646321D89202DE7FDCAB1A49833A26C68027C228437AE04FB2A8';

但用@>操作符过滤f2时无匹配结果，且发现f2存储为类似'\30140912....'的转义文本格式，与f1的十六进制存储格式不一致。

解决方案 #

问题根源 #

使用{}包裹的字符串数组语法插入bytea[]时，PostgreSQL会将数组内的0x开头字符串当作普通文本转换为bytea，即存储的是字符串"0xA669..."本身的字节，而非解析十六进制后的二进制数据，这与f1的存储逻辑完全不同，导致查询无法匹配。

正确插入方式 #

需使用ARRAY[]构造器，显式将每个十六进制字符串转换为bytea类型，有两种写法：

写法一：显式类型转换

INSERT INTO my_table (f1,f2) 
VALUES (
  E'\\xF7C26945C70B646321D89202DE7FDCAB1A49833A26C68027C228437AE04FB2A8',
  ARRAY[
    '\xA6697E974E6A320F454390BE03F74955E8978F1A6971EA6730542E37B66179BC'::bytea,
    '\x4B52414B00000000000000000000000000000000000000000000000000000000'::bytea
  ]
);

写法二：使用转义十六进制格式并统一转换数组类型

INSERT INTO my_table (f1,f2) 
VALUES (
  E'\\xF7C26945C70B646321D89202DE7FDCAB1A49833A26C68027C228437AE04FB2A8',
  ARRAY[
    E'\\xA6697E974E6A320F454390BE03F74955E8978F1A6971EA6730542E37B66179BC',
    E'\\x4B52414B00000000000000000000000000000000000000000000000000000000'
  ]::bytea[]
);

验证查询 #

插入完成后，原查询语句即可正常返回结果：

SELECT * FROM my_table 
WHERE f2 @> ARRAY ['\xA6697E974E6A320F454390BE03F74955E8978F1A6971EA6730542E37B66179BC'::bytea];

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