如何在PostgreSQL中用IN子句实现多列与部分字符串匹配查询

假设有如下表格：

需要通过元组数组进行查询，每个元组格式为("john", "i1", "docs")：

• col1和col2需精确匹配
• col3支持正则或LIKE模糊匹配

期望的查询语句示例如下：

SELECT *
FROM table
WHERE (col1, col2, col3) IN (('john', 'i1', 'docs%'), ('marry', 'i2', 'images%'))

已知条件：

• 元组数组长度较长，来源于JavaScript数组
• 表的主键由所有列组成，希望查询时利用该主键

请问在PostgreSQL中是否有优雅的实现方式？

有多种优雅的实现方式，核心是适配JavaScript数组的传参逻辑，同时最大化利用主键索引的性能：

1. 多维数组+UNNEST关联查询 #

把JavaScript传来的元组数组转换成PostgreSQL的text[][]多维数组，通过UNNEST展开后与原表关联匹配：

SELECT t.*
FROM target_table t
JOIN UNNEST(
  ARRAY[
    ['john', 'i1', 'docs'],
    ['marry', 'i2', 'images']
  ]::text[][]
) AS filters(col1, col2, col3_prefix)
ON t.col1 = filters.col1
AND t.col2 = filters.col2
AND t.col3 LIKE filters.col3_prefix || '%'

这种写法的优势：

• 直接对应JavaScript的数组结构，前端传参时只需将数组序列化为JSON，后端转成PostgreSQL多维数组即可
• 主键(col1, col2, col3)的B-tree索引天然支持col1/col2精确匹配+col3前缀模糊匹配，查询会自动走主键索引，性能拉满

2. 行类型数组+EXISTS子查询 #

如果偏好更紧凑的写法，可以将查询条件封装成行类型数组，结合EXISTS子查询实现：

SELECT *
FROM target_table t
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(
    ARRAY[
      ROW('john', 'i1', 'docs'),
      ROW('marry', 'i2', 'images')
    ]::(text, text, text)[]
  ) AS filters(col1, col2, col3_prefix)
  WHERE t.col1 = filters.col1
    AND t.col2 = filters.col2
    AND t.col3 LIKE filters.col3_prefix || '%'
)

3. 超大量元组的优化 #

如果元组数组长度达到数万级，建议先将数据插入临时表，再与原表关联：

-- 创建临时表
CREATE TEMP TABLE temp_filters (col1 text, col2 text, col3_prefix text);
-- 批量插入前端传来的元组数据（可通过COPY或批量INSERT实现）
-- 关联查询
SELECT t.*
FROM target_table t
JOIN temp_filters f
ON t.col1 = f.col1
AND t.col2 = f.col2
AND t.col3 LIKE f.col3_prefix || '%';

这种方式能避免数组参数过长导致的解析性能问题，同时依然可以利用主键索引。

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