From: | "Frank D(dot) Engel, Jr(dot)" <fde101(at)fjrhome(dot)net> |
---|---|
To: | 'PgSql General' <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Unique Index |
Date: | 2005-01-20 16:11:55 |
Message-ID: | 00EE9545-6AFE-11D9-AAA7-0050E410655F@fjrhome.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----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(at)gmail(dot)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(at)gmail(dot)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(at)fjrhome(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Turner | 2005-01-20 16:23:00 | Re: [GENERAL] Re: Oracle and Postgresql Play Nice Together on Same Computer |
Previous Message | Frank D. Engel, Jr. | 2005-01-20 16:09:02 | Re: [ADMIN] Oracle and Postgresql Play Nice Together on Same Computer? |