如何匹配PostgreSQL中复合类型数组元素与表的复合主键

错误原因 #

unnest(elements)返回的结果是单列的element_pk_t复合类型值，而IN子句左侧是3个独立列组成的行表达式，两侧列数不匹配所以触发报错。

方案1：使用ANY运算符直接匹配复合类型（推荐） #

该写法无需拆分复合类型，性能更优、语法更简洁：

SELECT *
FROM element
WHERE ROW(pk1, pk2, pk3)::element_pk_t = ANY(
    SELECT elements FROM collection WHERE id = 1
);

将element表的三个主键字段打包为element_pk_t复合类型，直接和collection表取出的elements数组做匹配，ANY运算符会校验左值是否存在于右侧数组的任意元素中。

方案2：展开复合类型为多列匹配 #

如果需要把复合类型拆为独立列做IN匹配，可以用(复合类型).*语法展开字段：

SELECT *
FROM element
WHERE (pk1, pk2, pk3) IN (
    SELECT (unnest(elements)).*
    FROM collection
    WHERE id = 1
);

(unnest(elements)).*会把每个element_pk_t类型的元素拆为pk1、pk2、pk3三列返回，和左侧行表达式的列数对齐后即可正常执行匹配。

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