Last active
November 23, 2022 19:25
-
-
Save miguelmota/6d40be2ecb083507de1d073443154610 to your computer and use it in GitHub Desktop.
PostgreSQL find gaps in sequence
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- table is 'blocks' | |
-- column is 'number' | |
SELECT | |
gap_start, gap_end FROM ( | |
SELECT number + 1 AS gap_start, | |
next_nr - 1 AS gap_end | |
FROM ( | |
SELECT number, lead(number) OVER (ORDER BY number) AS next_nr | |
FROM blocks | |
) nr | |
WHERE nr.number + 1 <> nr.next_nr | |
) AS g | |
UNION ALL ( | |
SELECT | |
0 AS gap_start, | |
number AS gap_end | |
FROM | |
blocks | |
ORDER BY | |
number | |
ASC LIMIT 1 | |
) | |
ORDER BY | |
gap_start |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
thanks for sharing this, it's exactly what I needed. I'm storing all ethereum blocks in a DB and finding/filling any gaps in the sequence on a cron :)