PostgreSQL中如何通过排序后的字符串数组匹配查询数据

要实现按空格拆分后排序再匹配的需求，核心是将列值与查询条件都转换为排序后的字符串数组，再比较两者是否相等。具体可以通过unnest+array_agg的组合来实现数组排序：

直接查询写法 #

假设你的表名为your_table，存储空格分隔字符串的列名为target_column，可以用以下SQL直接查询：

SELECT *
FROM your_table
WHERE (
  -- 将列值拆分后排序并重新聚合为数组
  SELECT array_agg(elem ORDER BY elem)
  FROM unnest(string_to_array(target_column, ' ')) AS elem
) = (
  -- 将查询条件拆分后排序并重新聚合为数组
  SELECT array_agg(elem ORDER BY elem)
  FROM unnest(string_to_array('A.P A.Y F.K', ' ')) AS elem
);

逻辑说明 #

1. string_to_array(xxx, ' ')：把空格分隔的字符串拆分为无序数组
2. unnest(xxx)：将数组展开为单行记录
3. array_agg(elem ORDER BY elem)：将展开后的记录按字符串排序，重新聚合成有序数组
4. 两边有序数组相等时，说明原字符串包含的元素完全一致（顺序无关）

性能优化方案 #

如果需要频繁执行这类查询，建议给表添加一个生成列存储预排序后的数组，避免每次查询都重复计算：

-- 添加生成列
ALTER TABLE your_table ADD COLUMN sorted_target_array text[] GENERATED ALWAYS AS (
  (SELECT array_agg(elem ORDER BY elem) FROM unnest(string_to_array(target_column, ' ')) AS elem)
) STORED;

-- 后续查询直接使用生成列
SELECT * FROM your_table 
WHERE sorted_target_array = (
  SELECT array_agg(elem ORDER BY elem) FROM unnest(string_to_array('A.P A.Y F.K', ' ')) AS elem
);

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