sql postgresql google-bigquery gaps-and-islands gaps-in-data

Given a sequence of numbers how to identify the missing numbers

I would like to get all missing numbers in a sequence of numbers.
Just wondering if there is a better approach than below?

SELECT x
  FROM 
  (
    SELECT x,
           LAG(x,1) OVER ( ORDER BY x ) prev_x
      FROM 
       ( SELECT * FROM
        ( SELECT 1 AS x ),
        ( SELECT 2 AS x ),
        ( SELECT 3 AS x ),
        ( SELECT 4 AS x ),
        ( SELECT 5 AS x ),
        ( SELECT 6 AS x ),
        ( SELECT 8 AS x ),
        ( SELECT 10 AS x ),
        ( SELECT 11 AS x )
       )
  ) 
 WHERE x-prev_x > 1;

Solution

The shortest solution in Postgres is with standard SQL EXCEPT:

WITH tbl(x) AS (SELECT unnest ('{1,2,3,4,5,6,8,10,11}'::int[]))
-- the CTE provides a temp table - might be an actual table instead
SELECT generate_series(min(x), max(x)) FROM tbl
EXCEPT ALL
TABLE  tbl;

The set-returning function unnest() is Postgres specific and just the shortest syntax to provide your set of numbers as table.

Also works with duplicates or NULL values in the data.

TABLE tbl is (standard SQL!) short syntax for SELECT * FROM tbl:

Is there a shortcut for SELECT * FROM in psql?

Related (with more explanation):