Re: Can I CONSTRAIN a particular value to be UNIQUE?

Tom Lane wrote:

> 2. In PG 7.2, partial indexes work again, so you could do
>
>         CREATE UNIQUE INDEX ... (subject, target, trial) WHERE success = 1;
>
> Which of these is better depends on how many of each sort of row
> you expect to have, and whether you'll be doing any queries wherein
> you could use an index on subject/target/trial for the non-success
> rows.
>

Oooooo! Option #2 seems very nice for what I'm trying to do. Would there be
any problem with me using

CREATE UNIQUE INDEX pk1 ON table1 (subject, target, trial) WHERE success = 1;
CREATE UNIQUE INDEX pk0 ON table1 (subject, target, trial) WHERE success = 0;

i.e. 2 partial indicies on the same table?


I know PG7.2 is still in beta, but it might be worthwhile for me to make the
jump from 7.1.3 for this feature. If I upgrade to the beta, will I have to do
a dump/restore again when the official 7.2 release comes out? I know I'll have
to do one for the beta anyway, but I'd hate to have to do it twice (especially
since the 7.2 official release will probably come out soon).

Thanks Tom.
-Tony