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

"G. Anthony Reina" <reina(at)nsi(dot)edu> writes:
> 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?

Not at all, but if those are the only two possible values of "success"
then it would seem this is the same as one non-partial unique index.
If you're thinking of allowing other, unconstrained success values then
this could make sense.

> 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?

We try to avoid forcing initdb after we start the beta cycle, but we
make no guarantees until we go final. FWIW, at this point I doubt we'll
need to do that.

regards, tom lane