Re: Unique Index

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