PostgreSQL根据现有列与指定列表筛选匹配值生成新列方法

实现逻辑 #

原表Table1的Habitat列是逗号拼接的多值字符串，要提取命中目标列表[Zoo, Home]的值，核心分三步：

• 把Habitat列的字符串按逗号拆分，自动去除每个值前后的冗余空格，避免格式问题导致匹配失败
• 拿拆分后的值集合和目标匹配列表做交集计算，只保留两边都存在的值
• 把交集结果重新拼接成逗号分隔的字符串，和原列格式保持一致返回

可直接运行的SQL代码 #

下面的写法兼容PostgreSQL 9.x及以上所有正式版本，不需要额外安装扩展：

-- 匹配配置区，后续要改匹配目标直接改这里的数组值即可
WITH match_conf(target_vals) AS (
    VALUES (ARRAY['Zoo', 'Home']::text[])
)
SELECT
    t.Animal,
    array_to_string(
        ARRAY(
            -- 拆分清洗Habitat值后和目标列表取交集
            SELECT clean_habitat
            FROM unnest(ARRAY(SELECT trim(unnest(string_to_array(t.Habitat, ','))))) AS clean_habitat
            INTERSECT
            SELECT unnest(conf.target_vals)
            FROM match_conf conf
        ),
        ', ' -- 拼接分隔符，和原数据格式保持一致
    ) AS matched_habitat
FROM Table1 t, match_conf;

对应测试数据的返回结果 #

按照题目给出的样例数据，执行后返回的结果集如下：

可选调整 #

• 如果不需要保留完全没匹配到任何目标值的行，只需要在SQL末尾增加WHERE条件过滤空结果即可：

WHERE array_length(
    ARRAY(
        SELECT clean_habitat
        FROM unnest(ARRAY(SELECT trim(unnest(string_to_array(t.Habitat, ','))))) AS clean_habitat
        INTERSECT
        SELECT unnest(conf.target_vals)
        FROM match_conf conf
    ),
1) > 0

• 如果需要匹配结果和原Habitat列里的出现顺序保持一致，可以把取交集的数组逻辑替换为按顺序过滤的写法，避免INTERSECT默认排序打乱原有顺序：

ARRAY(
    SELECT clean_habitat
    FROM unnest(ARRAY(SELECT trim(unnest(string_to_array(t.Habitat, ','))))) WITH ORDINALITY AS c(clean_habitat, pos)
    WHERE clean_habitat = ANY((SELECT target_vals FROM match_conf))
    ORDER BY pos
)

内容的提问来源于stack exchange，提问作者Peter Tettey Yamak