How Do I Find a Gap In a Sequence in PostgreSQL?

Question

I have a table of items with an id and a number_col. There are plenty of questions on Stackoverflow for finding gaps in IDs, but what I'm looking for is a gap in the number. For example, if I have 3 items:

id | number_col
===============
1  | 1
1  | 2
1  | 4

I need a SQL query that returns id = 1, number_col = 3 as missing. The query should look at the max value in number_col for each id. Since 4 is the max number_col value for id 1 in my example, it should not return that 1, 5 is missing.

not going to write this out for you... but start with a query that returns id's where the count of rows does not equal the max number_col value. then for those, use a LEAD function to look for the next number and check if it is the current number +1. — Randy, Feb 20 '19 at 15:37
http://www.silota.com/docs/recipes/sql-gap-analysis-missing-values-sequence.html — Arto Bendiken, Oct 30 '21 at 09:17

score 2 · Accepted Answer · answered Feb 20 '19 at 15:45

2

If you want ranges of missing values, then:

select number_col + 1 as first_missing, (next_nc - 1) as last_missing
from (select t.*, lead(number_col) over (partition by id order by number_col) as next_nc
      from t
     ) t
where next_nc <> number_col + 1

answered Feb 20 '19 at 15:45

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 2 · edited Dec 09 '21 at 02:51

2

you can use this query

select * 
from table_name 
where col_name - 1 NOT IN (select col_name from table_name) 
GROUP BY col_name having col_name > MIN(col_name);

GROUP BY col_name having col_name > MIN(col_name) to discard the first row.

edited Dec 09 '21 at 02:51

Ihor Konovalenko

1,298
2
16
21

answered Dec 08 '21 at 10:08

Abubakr Sokarno

21
1

How Do I Find a Gap In a Sequence in PostgreSQL?

2 Answers2

Linked