如何筛选PostgreSQL数组中不存在于数据库的元素

你的核心需求是从给定数组中找出未出现在entity_coordinates表entity_number字段中的元素，原查询使用FULL OUTER JOIN会返回所有匹配与不匹配的项，无法直接得到目标结果，以下是两种可行的修正方案：

方案一：基于原查询修改（LEFT JOIN + 空值过滤） #

将原查询的FULL OUTER JOIN改为LEFT JOIN，并通过WHERE条件筛选出表中无匹配的数组元素：

WITH res AS (SELECT entity_number FROM entity_coordinates WHERE entity_number IN (
    'MG1735401016/6',
    'NON-EXIST-1',
    'P171025002876-1',
    'P170321400780-1',
    'NON-EXIST-2'
))
SELECT item_id
FROM unnest(ARRAY[
    'MG1735401016/6',
    'NON-EXIST-1',
    'P171025002876-1',
    'P170321400780-1',
    'NON-EXIST-2'
]) item_id
LEFT JOIN res ON res.entity_number = item_id
WHERE res.entity_number IS NULL;

原理：LEFT JOIN会保留数组中的所有元素，能匹配到表数据的项会填充entity_number值，未匹配的项该字段为NULL，通过过滤NULL即可得到目标结果。

方案二：使用NOT EXISTS（更简洁高效） #

直接针对数组展开后的每一项，检查表中是否存在对应记录，不存在则返回：

SELECT item_id
FROM unnest(ARRAY[
    'MG1735401016/6',
    'NON-EXIST-1',
    'P171025002876-1',
    'P170321400780-1',
    'NON-EXIST-2'
]) item_id
WHERE NOT EXISTS (
    SELECT 1
    FROM entity_coordinates
    WHERE entity_coordinates.entity_number = item_id
);

原理：NOT EXISTS子查询会判断当前数组元素是否在表中存在，不存在则保留该元素。若entity_number字段建有索引，此查询性能会更优。

额外说明 #

如果数组中存在重复元素，且需要返回去重后的结果，可在SELECT后添加DISTINCT：

SELECT DISTINCT item_id
FROM unnest(ARRAY[...]) item_id
WHERE NOT EXISTS (...)

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