Re: Unique Index

["Frank D. Engel, Jr." <fde101@xxxxxxxxxxx>]

-----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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxx>

$ 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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
     message can get through to the mailing list cleanly