> Merlin Moncure wrote:
> > I need a fast way (sql only preferred) to solve the following
> > I need the smallest integer that is greater than zero that is not in
> > column of a table.
> > I've already worked out a query using generate_series (not scalable)
> > 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
> 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.
pgsql-performance by date
|Next:||From: Merlin Moncure||Date: 2005-06-28 15:39:53|
|Subject: Re: tricky query|
|Previous:||From: Michael Stone||Date: 2005-06-28 15:27:50|
|Subject: read block size|