Re: Partial Indices vs. mixing columns and functions

Mike Mascari <mascarm@mascari.com> writes:
>> 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;

Just for the record: with CVS tip,

regression=# create table foo(value int, deactive int);
CREATE
regression=# CREATE UNIQUE INDEX i_foo ON foo(value)
regression-#  WHERE deactive IS NULL;
CREATE
regression=# insert into foo values (1,2);
INSERT 146307 1
regression=# insert into foo values (1,2);
INSERT 146308 1
regression=# insert into foo values (1,null);
INSERT 146309 1
regression=# insert into foo values (1,null);
ERROR:  Cannot insert a duplicate key into unique index i_foo
regression=# insert into foo values (2,null);
INSERT 146311 1
regression=# insert into foo values (2,null);
ERROR:  Cannot insert a duplicate key into unique index i_foo
regression=#

            regards, tom lane