PostgreSQL查询：筛选data数组不含指定值或格式的记录

表结构 #

需求 #

• 筛选所有data列中不包含对应id值的行
• 至少筛选出data列中不包含以org:开头且以:basic结尾文本的记录

当前错误尝试 #

执行以下SQL后返回所有行，不符合预期：

SELECT * FROM t_permission WHERE 'org:%:basic' NOT LIKE ANY (data)

解决方案 #

1. 筛选data中不包含对应id的行 #

假设data是PostgreSQL数组类型（从{...}格式判断），直接检查id是否不在data的元素集合中：

SELECT * FROM t_permission WHERE NOT (id = ANY(data));

执行结果返回第2、3行，第1行因data包含自身id被排除。

2. 筛选data中无org:*:basic格式元素的行 #

通过unnest展开数组元素，检查不存在符合模式的元素：

SELECT * FROM t_permission 
WHERE NOT EXISTS (
    SELECT 1 FROM unnest(data) AS elem
    WHERE elem LIKE 'org:%:basic'
);

或者用ALL关键字简化写法：

SELECT * FROM t_permission 
WHERE ALL(data) NOT LIKE 'org:%:basic';

执行结果返回第2、3行，这两行的data中没有符合org:*:basic格式的元素。

错误原因说明 #

原SQL逻辑完全颠倒：'org:%:basic' NOT LIKE ANY (data)是判断模式字符串是否不匹配data中的任意元素，但LIKE的正确用法是元素 LIKE 模式，而非模式匹配元素，这导致所有行被错误返回。

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