PostgreSQL中处理含范围的邮编列WHERE条件查询问题

问题场景 #

我在parcels表中有一个类型为text的zips列，用户可填入以下三种格式的内容：

• 单个邮编，如'10001'
• 多个逗号分隔的邮编，如'10002,10010,10015'
• 包含连字符分隔的邮编范围（可带引号），如'10001,"10010-10025"'

当前使用的SQL仅能处理逗号分隔的单邮编匹配，无法识别邮编范围：

select * 
from parcels 
where "10015" = ANY(string_to_array(parcels.zips, ','))

需要实现的逻辑：将zips列按逗号拆分后，逐个检查每个元素——若元素包含'-'，则判断目标邮编是否在该范围内；否则判断是否与目标邮编完全相等，所有条件以OR连接。

解决方案SQL #

SELECT *
FROM parcels p
WHERE EXISTS (
    SELECT 1
    FROM unnest(string_to_array(p.zips, ',')) AS elem
    CROSS JOIN LATERAL (
        SELECT trim('"' FROM elem) AS clean_elem
    ) AS ce
    CROSS JOIN LATERAL (
        SELECT 
            split_part(ce.clean_elem, '-', 1) AS zip_start,
            split_part(ce.clean_elem, '-', 2) AS zip_end
    ) AS se
    WHERE 
        ce.clean_elem = '10015'
        OR 
        (zip_end IS NOT NULL AND '10015' BETWEEN zip_start AND zip_end)
);

逻辑说明 #

1. 拆分元素：用unnest(string_to_array(p.zips, ','))将zips列的内容按逗号拆分成独立行，逐个处理每个邮编项
2. 清理格式：通过trim('"' FROM elem)去掉元素中的引号，统一格式
3. 拆分范围：用split_part将带'-'的元素拆分成起始邮编和结束邮编
4. 匹配判断：通过OR连接两种匹配规则——要么是完全匹配的单个邮编，要么是落在范围内的邮编；只要有一个元素满足条件，就返回对应的记录

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