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

On 29 Nov 2001, Tony Reina wrote:

> I have a table where I'd like to store only one instance where a trial
> was successful, but all instances where the trial failed. The success
> or failure is indicated by the field called 'success'. There should be
> only one unique case for each trial where success = 1, but an
> undefined number of cases where success = 0.
>
> e.g.
> CREATE TABLE table_1 (
> subject    text,
> target     int2,
> trial      int4,
> success    int2,
> data       float4 );
>
> CREATE UNIQUE INDEX pktable_1 ON table_1 (subject, target, trial);
>
> I'd like to have some way for SQL to give me an error if I attempt to
> insert more than one instance of a given subject, target, and trial
> where success = 1, but would allow me to insert as many instances of
> the same subject, target, and trial where success = 0.
>
> Is there a way to do this at table creation time?

Not really within what we provide (theoretically you could probably do
it with a subselect in check I think). You'll probably will want to look
at writing a plpgsql trigger to do the check.