PostgreSQL中如何筛选字符串数组类型IP列的指定范围记录

错误原因梳理 #

你遇到的语法错误和查询失效主要来自三个问题：

1. 数组无法直接与inet类型比较：直接对数组列使用BETWEEN或比较运算符（>/<）不生效，因为数组类型和inet类型无法直接做逻辑判断。
2. 重复的类型转换：inet('192.168.1.1')::inet属于冗余转换，inet()函数本身已经返回inet类型，再用::inet强制转换会触发语法错误。
3. CTE语句格式错误：你之前的CTE末尾多了分号，导致后续SELECT无法关联到定义的临时表。

正确实现方式 #

方式一：展开数组后筛选关联（适合需要查看具体匹配IP的场景）

先通过unnest展开数组，筛选出符合IP范围的记录ID，再关联原表去重：

WITH expanded_ips AS (
    SELECT id, unnest(ip) AS ip_address
    FROM your_table
), filtered_ips AS (
    SELECT id
    FROM expanded_ips
    -- 两种转换方式二选一即可，不要重复转换
    WHERE inet(ip_address) BETWEEN '192.168.1.1'::inet AND '192.168.1.255'::inet
    -- 等价写法：WHERE inet(ip_address) >= inet('192.168.1.1') AND inet(ip_address) <= inet('192.168.1.255')
)
SELECT DISTINCT yt.*
FROM your_table yt
JOIN filtered_ips fi ON yt.id = fi.id;

方式二：使用EXISTS子查询（更简洁，性能更优）

无需展开数组生成临时表，直接通过EXISTS判断数组中是否存在符合范围的IP：

SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM unnest(ip) AS single_ip
    WHERE inet(single_ip) BETWEEN '192.168.1.1'::inet AND '192.168.1.255'::inet
);

报错查询的修正版本 #

针对你在pgAdmin中报错的查询，修正重复转换问题后即可正常执行：

-- 修正后查询
select principal_ip
from event
cross join unnest(principal.ip) as principal_ip
WHERE inet(principal_ip) >= '192.168.1.1'::inet;

或使用inet()函数转换目标IP：

select principal_ip
from event, unnest(principal.ip) as principal_ip
WHERE inet(principal_ip) >= inet('192.168.1.1');

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