Message-ID: <42C167C6.5070108@arbash-meinel.com>
Date: Tue, 28 Jun 2005 10:07:50 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: tricky query
References: <6EE64EF3AB31D5448D0007DD34EEB3415C2BEC@Herge.rcsinc.local>
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3415C2BEC@Herge.rcsinc.local>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig993DD4397E8462A824F32F2F"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig993DD4397E8462A824F32F2F
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
=:->


--------------enig993DD4397E8462A824F32F2F
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCwWfGJdeBCYSNAAMRAsNFAKCdYfJ4aZjw0bNhNnCcugH8qnn2ngCfTWTz
Aus1toBGdKn7YRJA8ZePgmg=
=JxB0
-----END PGP SIGNATURE-----

--------------enig993DD4397E8462A824F32F2F--