PostgreSQL：如何使用generate_series()找出列中缺失的数字？

SELECT commandid 
FROM results 
WHERE NOT EXISTS (
    SELECT * 
    FROM generate_series(0,119999) 
    WHERE generate_series = results.commandid 
    );

我有一个专栏results类型的int但各种测试失败，没有添加到表中。我想创建一个返回列表的查询commandid中找不到的results。我认为上面的查询可以满足我的要求。但是，如果我使用的范围超出了预期的可能范围，它甚至不起作用commandid（如负数）。

给定样本数据：

create table results ( commandid integer primary key);
insert into results (commandid) select * from generate_series(1,1000);
delete from results where random() < 0.20;

这有效：

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
WHERE NOT EXISTS (SELECT 1 FROM results WHERE commandid = s.i);

这个替代公式也是如此：

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
LEFT OUTER JOIN results ON (results.commandid = s.i) 
WHERE results.commandid IS NULL;

在我的测试中，上述两者似乎都会产生相同的查询计划，但您应该使用以下命令与数据库上的数据进行比较EXPLAIN ANALYZE看看哪个最好。

解释

请注意，而不是NOT IN我用过NOT EXISTS在一个公式中包含一个子查询，以及一个普通的OUTER JOIN在另一个。数据库服务器更容易优化这些，并且避免了可能出现的令人困惑的问题NULLs in NOT IN.

我最初更喜欢OUTER JOIN公式化，但至少在 9.1 中我的测试数据是NOT EXISTS形式优化到相同的计划。

两者都会比NOT IN当系列很大时（就像您的情况一样），请使用下面的公式。NOT IN用于要求 Pg 进行线性搜索IN列出每个正在测试的元组，但对查询计划的检查表明 Pg 现在可能足够聪明，可以对它进行哈希处理。这NOT EXISTS（转化为JOIN由查询规划器）和JOIN工作得更好。

The NOT IN在存在 NULL 的情况下，表述会令人困惑commandids 并且可能效率低下：

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
WHERE s.i NOT IN (SELECT commandid FROM results);

所以我会避免它。对于 1,000,000 行，另外两个在 1.2 秒内完成，而NOT IN配方运行受 CPU 限制，直到我感到无聊并取消它。