Re: Partial Indices vs. mixing columns and functions

Mike Mascari <mascarm@mascari.com> writes:
> To enforce uniqueness because
> deactive is NULL, I cannot just create an index like:

> CREATE UNIQUE INDEX i_foo (value, deactive);

It's not clear to me what you are really after here.  You *can* create a
unique index, even though 'deactive' is allowed to be NULL --- what will
happen is that rows containing NULL will never conflict with other
entries.  Is that what you want, or are you trying to say that you don't
want more than one row with 'deactive' NULL for any given 'value' value?

> Or will Martijn van Oosterhout's new Partial Indices work allow me to
> create a unique index like:

> CREATE UNIQUE INDEX i_foo ON foo(value)
> WHERE deactive IS NULL;

This would seem to imply that you want the latter.

As Martijn remarks elsewhere, the above would not be allowed by the
existing code for partial indexes.  But there is no good reason for
that.  The reason for the restriction is that the planner's code for
determining whether a partial index can be used in a query is pretty
limited (with good reason; we don't want to be letting loose a full-tilt
automated theorem prover on every query...).  But the above example
demonstrates that an index can be useful even if it's never used in
a query!

I would say that this example shows that we should rip out the
restrictions on the form of the predicate, and just ensure that the
planner code will give up cleanly if the predicate is not of a form
it can handle.

            regards, tom lane