Re: Partial Indices vs. mixing columns and functions

Martijn van Oosterhout wrote:
>
> On Wed, Jul 11, 2001 at 04:09:51AM -0400, Mike Mascari wrote:
> > Hello,
> >
> > I have created table/view pairs like:
>
> [snip]
>
> Yes, creating a unique partial index should be possible and will do what you
> want I think, (I couldn't totally follow what you meant).
>
> However, partial indicies will not support the IS NULL predicates, for the
> same reason that IS NULL cannot use an index for lookups.
>
> I'd love to fix that but that's going to be hard (or rather, I havn't
> thought of an easy way to do it :).
>
> Maybe someone has a better solution.

What are the limits of the WHERE expression? Must they be composed of
constant expressions or can they be more complex?

For example,

would I be able to do:

CREATE TABLE foo (
key integer not null,
value text not null,
active timestamp not null default now(),
deactive timestamp not null default now()
);

CREATE VIEW v_foo
AS SELECT * FROM foo WHERE active = deactive;

CREATE UNIQUE INDEX i_foo ON foo(value)
WHERE active = deactive;

Or is the WHERE clauses limited to constant expressions like a check
condition:

CREATE UNIQUE INDEX i_foo ON foo(value)
WHERE value = 'Bar';

Thanks for any info,

Mike Mascari
mascarm@mascari.com

P.S.

I subitted the TRUNCATE TABLE patch back in 6.5. It literally truncates
the underlying relation and all indices using the smgr layer and then
rebuilds the indices. I remember seeing the partial indices work at the
time and attempted to preserve the functionality in the patch, but could
never test it since they had been deactivated well before then. I'm sure
Tom Lane has removed my cruft since then, but it might be a source of a
needed test.