CODEWITHSUNDEEP
databasepostgresql
joão
joão

Reputation: 21

How to find missed ids in my table?

How to find missed ids in my table? for example right now ids are 

1
3
8

my aim is find missed values

2
4
5
6
7

Upvotes: 1

Views: 65

Answers (3)

Evan Carroll
Evan Carroll

Reputation: 1

For style reasons only, this also results in an anti-join (like @Oto Shavadze's answer). You can pick which style you like more. NOT EXISTS should be preferred where applicable because

  1. it reduces the set, and NOT EXISTS is explicit in that. A left-join can have the effect of growing the set.
  2. it's conditions are contained, there is no chance the inner.id = outer.id can drift away from the clause.
  3. the inner table is not made available anywhere else in the query.

Here is an example,

SELECT s.v
FROM generate_series(1, (SELECT max(id) FROM yourTable))
  AS s(v)
WHERE NOT EXISTS (
  SELECT 1
  FROM yourTable
  WHERE s.v = id
);

Upvotes: 0

Oto Shavadze
Oto Shavadze

Reputation: 42783

SELECT s.v from generate_series(1, (select max(id) from your_table))  s(v)
left join your_table
on s.v = your_table.id
WHERE your_table.id IS NULL

Upvotes: 1

Adam
Adam

Reputation: 5599

See generate_series(start, stop) function. Just LEFT JOIN the series with your ids to get the missing ones.

Or use EXCEPT operator

SELECT
    generate_series(1, MAX(id)) AS id
FROM
    the_table
EXCEPT
SELECT
    id
FROM
    the_table

Upvotes: 1

Related Questions