PostgreSQL Get holes in index column

Question

I suppose it is not easy to query a table for data which don't exists but maybe here is some trick to achieve holes in one integer column (rowindex).
Here is small table for illustrating concrete situation:

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;

With showed query I get all used indexes listed.
But I would like to get (say) first five indexes which is missed so I can use them for insert new data at desired rowindex.
In concrete example result will be: 2, 4, 8, 9, 12 what represent indexes which are not used.

Is here any trick to build a query which will give n number of missing indexes?
In real, such table may contain many rows and "holes" can be anywhere.

Would this work for you? http://stackoverflow.com/questions/1312101/how-to-find-a-gap-in-running-counter-with-sql — Mark Davidson, Dec 24 '15 at 10:56
One option, join with the output from `generate_series(1, MAX(rowindex)`: http://www.postgresql.org/docs/current/static/functions-srf.html — Wolph, Dec 24 '15 at 11:02

score 4 · Accepted Answer · answered Dec 24 '15 at 11:02

You can do this by generating a list of all numbers using generate_series() and then check which numbers don't exist in your table.

This can either be done using an outer join:

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;

or an not exists query:

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);

I have used a hardcoded lower bound for generate_series() so that you would also detect a missing rowindex that is smaller than the lowest number.

WOW! Very interesting and fully workable, both examples! I have to study "generate_series" which I never used before. Also I hope this will work acceptable on large amount of data. Thank you. — Wine Too, Dec 24 '15 at 11:17

PostgreSQL Get holes in index column

1 Answers1