> 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. > > > > 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? > Probably not, but I thought about this "brute-force" approach... :-) > This should work well provided that: > > - you have a finite number of integers. Your column should have a biggest > integer value with a reasonable maximum like 100,000 or 1,000,000. > #define YOUR_MAX 99999 [...] :-) generate_series function does the same thing only a little bit faster (although less portable).
generate_series(m,n) returns set of integers from m to n with time complexity n - m. I use it for cases where I need to increment for something, for example: select now()::date + d from generate_series(0,355) as d; returns days from today until 355 days from now. Merlin ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])