PostgreSQL WHERE子句中数组字段多值匹配查询实现

你之前尝试的(8,9) in any(subject_id)无法正常执行，核心原因是any()操作符仅支持对数组的单个标量元素做匹配，左侧传入的多值元组和数组元素的标量类型不匹配，无法完成对比。PostgreSQL提供了原生的数组操作符，可以覆盖绝大多数多值匹配的筛选需求，具体写法根据匹配逻辑分为两类：

分场景实现写法 #

• 任意值命中即匹配（OR逻辑）
  只要subject_id数组中包含候选值集合里的任意一个值，就满足筛选条件。
  使用&&数组重叠操作符实现，比如要筛选subject_id包含8或者9的记录：
  select * from stud where subject_id && array[8,9];
  该操作符支持GIN索引优化，大表场景下可获得非常高的查询性能。
• 全部值命中才匹配（AND逻辑）
  要求subject_id数组必须完整包含所有给定的目标值，缺任意一个都不满足筛选条件。
  使用@>数组包含操作符实现，比如要筛选subject_id同时包含7和9的记录：
  select * from stud where subject_id @> array[7,9];

写法等价性说明 #

你之前掌握的单值筛选语句9=any(subject_id)，和以下两种写法的执行结果完全等价：

• subject_id @> array[9]
• array[9] <@ subject_id
  单值场景下三种写法可以通用，但多值筛选时必须使用对应的数组操作符，不能直接套用元组匹配的语法。

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