I would suggest something like this, don't know how fast it is ... :

SELECT (ID +1) as result FROM my_table
WHERE (ID+1) NOT IN (SELECT ID FROM my_table) as tmp
ORDER BY result asc limit 1;





"Merlin Moncure" <merlin.moncure@rcsonline.com>
Envoyé par : pgsql-performance-owner@postgresql.org
28/06/2005 16:21


Pour : <pgsql-performance@postgresql.org>
cc :
Objet : [PERFORM] tricky query


I need a fast way (sql only preferred) to solve the following problem:

I need the smallest integer that is greater than zero that is not in the
column of a table. In other words, if an 'id' column has values
1,2,3,4,6 and 7, I need a query that returns the value of 5.

I've already worked out a query using generate_series (not scalable) and
pl/pgsql. An SQL only solution would be preferred, am I missing
something obvious?

Merlin

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org