postgres query to return missing data?

Question

postgres newbie here.

I have almost sequential data in a table - e.g.column data

seq 1 2 4 5 7

I am trying to write a query that returns the missing sequential numbers - eg 3,6 in this case. Not having much joy. Any help appreciated.

Nick Barnes · Accepted Answer · 2017-04-28 00:22:20Z

2

You can use generate_series to construct the full sequence, then join back to your table to filter out the existing values:

SELECT seq FROM (
  SELECT generate_series(MIN(seq), MAX(seq)) FROM t
) s (seq)
LEFT JOIN t USING (seq)
WHERE t.seq IS NULL

answered Apr 28, 2017 at 0:22

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks Nick! much more elegant than what I was constructing.