Skip site navigation (1) Skip section navigation (2)

Re: tricky query

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Cosimo Streppone" <cosimo(at)streppone(dot)it>
Cc: "Postgresql Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: tricky query
Date: 2005-06-28 15:30:34
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3415C2BF5@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-performance
> 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

pgsql-performance by date

Next:From: Merlin MoncureDate: 2005-06-28 15:39:53
Subject: Re: tricky query
Previous:From: Michael StoneDate: 2005-06-28 15:27:50
Subject: read block size

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group