PostgreSQL find gaps in sequence

gaps.sql

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

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