From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Tony Reina <reina(at)nsi(dot)edu> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Can I CONSTRAIN a particular value to be UNIQUE? |
Date: | 2001-11-29 19:59:40 |
Message-ID: | 20011129115442.X45413-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-11-29 20:38:20 | Re: Can I CONSTRAIN a particular value to be UNIQUE? |
Previous Message | Bruce Momjian | 2001-11-29 19:14:25 | Re: date_part vs extract |