PostgreSQL 获取索引列中的漏洞

Question

我想查询表中不存在的数据并不容易，但也许这里有一些技巧可以在一个整数列(行索引)中实现空洞。
下面是说明具体情况的小表格:

DROP TABLE IF EXISTS examtable1;
CREATE TABLE examtable1 
   (rowindex integer primary key, mydate timestamp, num1 integer);

INSERT INTO examtable1 (rowindex, mydate, num1)
VALUES (1,  '2015-03-09 07:12:45', 1),
       (3,  '2015-03-09 07:17:12', 4),
       (5,  '2015-03-09 07:22:43', 1),
       (6,  '2015-03-09 07:25:15', 3),
       (7,  '2015-03-09 07:41:46', 2),
       (10,  '2015-03-09 07:42:05', 1),
       (11,  '2015-03-09 07:45:16', 4),
       (14,  '2015-03-09 07:48:38', 5),
       (15, '2015-03-09 08:15:44', 2);


SELECT rowindex FROM examtable1;

通过显示的查询，我列出了所有使用的索引。
但我想获得(比如说)前五个丢失的索引，这样我就可以使用它们在所需的行索引处插入新数据。
在具体示例中，结果将是:2、4、8、9、12 表示未使用的索引。

这里有什么技巧可以构建一个查询，该查询将给出 n 个缺失索引吗？
实际上，这样的表格可能包含很多行，“漏洞”可能在任何地方。

Answer 1

您可以通过使用 generate_series() 生成所有数字的列表来执行此操作，然后检查您的表中不存在哪些数字。

这可以使用外部连接来完成:

select nr.i as missing_index
from (
  select i
  from generate_series(1, (select max(rowindex) from examtable1)) i
) nr
  left join examtable1 t1 on nr.i = t1.rowindex
where t1.rowindex is null;

或不存在查询:

select i
from generate_series(1, (select max(rowindex) from examtable1)) i
where not exists (select 1 
                  from examtable1 t1
                  where t1.rowindex = i.i);

我为 generate_series() 使用了硬编码的下限，这样您也可以检测到小于最低数字的缺失行索引。