CODEWITHSUNDEEP
sqldatabasepostgresql
mvb13
mvb13

Reputation: 1584

Select ids that don't exist in a table

I have an ids range (it defines by startId and stopId). I have a table with some records(every record has an id - primaty key). Now I need to select all ids from specified range that don't exist in the table. I am using postgres database. Please suggest what options I have to perform such query.

Upvotes: 2

Views: 1418

Answers (4)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

You may look at generate_series() function.

Then use an except clause to get the difference.

select s.a from generate_series(<start>, <stop>) as s(a)
except
select id from <myTable>
where <yourClause>
--order by a

See SqlFiddle

Upvotes: 3

Guilherme
Guilherme

Reputation: 1146

You can use the: http://www.postgresql.org/docs/9.1/static/functions-srf.html

SELECT * FROM generate_series(startId,stopId) AS all_ids WHERE id NOT IN (SELECT id FROM table WHERE id 
>= startId AND id <= stopId) as existent_ids;

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Generate the range of numbers with generate_series. Then subtract the already used numbers.

SELECT generate_series(startId, stopId)
EXCEPT
SELECT id FROM mytable;

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23361

I didn't test it here because I did this query for a oracle database, I change it a bit to fit postgresql, so it should work.

 select startid, endid, (endid-startid)+1 amount from (
   select m.yourID + 1 as startid,
     (select min(yourID) - 1 from yourtable x where and x.yourID > m.yourID) as endid
   from yourtable m left join
       (select yourID-1 yourID from yourtable r) r on (m.yourID = r.yourID)
   where r.yourID is null
 ) x
 where endid is not null
 order by amount desc, startid

Let me know if it worked.

Upvotes: 0

Related Questions