CODEWITHSUNDEEP
postgresql
user3667169
user3667169

Reputation: 9

postgres query to return missing data?

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.

Upvotes: 0

Views: 278

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21356

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

Upvotes: 2

Related Questions