Re: Unique Index

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm sure this won't work for some reason, but something similar might;
why not create a unique index on a constant where all three are null;
something along these lines (in addition to the others):

CREATE UNIQUE INDEX foo_trio_index ON foo (1) WHERE c IS NULL AND a IS
NULL and b IS NULL;

On Jan 20, 2005, at 10:57 AM, Greg Stark wrote:

> Dawid Kuroczko <qnex42@gmail.com> writes:
>
>> Don't worry about "index bloat".  These additional indexes will be
>> used
>> only when your main (foo_abc_index) is not used, so there won't be
>> any duplicate data in them.
>
> The main index will have _all_ the tuples in them, even where some of
> the
> columns are NULL, so this will in fact use extra space. It will also
> cause
> extra i/o on every update of a record with NULL in one of the columns.
>
> To minimize the extra space you could make it
>
> Dawid Kuroczko <qnex42@gmail.com> writes:
>
>  CREATE UNIQUE INDEX foo_ab_index ON foo (a,b) WHERE c IS NULL AND a
> is NOT NULL and b IS NOT NULL;
>  CREATE UNIQUE INDEX foo_ac_index ON foo (a,c) WHERE b IS NULL AND a
> is NOT NULL and c IS NOT NULL;
>  CREATE UNIQUE INDEX foo_bc_index ON foo (b,c) WHERE a IS NULL AND b
> is NOT NULL and c IS NOT NULL;
>  CREATE UNIQUE INDEX foo_a_index ON foo (a)   WHERE a IS NOT NULL AND
> b IS NULL and c is NULL;
>  CREATE UNIQUE INDEX foo_b_index ON foo (b)   WHERE b IS NOT NULL AND
> a IS NULL and c is NULL;
>  CREATE UNIQUE INDEX foo_c_index ON foo (c)   WHERE c IS NOT NULL AND
> a IS NULL and b is NULL;
>
> To avoid indexing the same tuples in multiple indexes.
>
> None of this will prevent you from inserting multiple <null,null,null>
> records
> though.
>
>
> --
> greg
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB79hL7aqtWrR9cZoRAglUAJ9sT3SypLYDZhx6Dkysfr7aLHQttwCeNLs8
/J4jFlWMLcMMxbQ3/nj55eA=
=4Bbe
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com