Merlin Moncure wrote:
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
Not so bad. Try something like this: SELECT min(id+1) as id_new FROM table WHERE (id+1) NOT IN (SELECT id FROM table); Now, this requires probably a sequential scan, but I'm not sure how you can get around that. Maybe if you got trickier and did some ordering and limits. The above seems to give the right answer, though. I don't know how big you want to scale to. You might try something like: SELECT id+1 as id_new FROM t WHERE (id+1) NOT IN (SELECT id FROM t) ORDER BY id LIMIT 1; John =:->
signature.asc
Description: OpenPGP digital signature
