From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tony Reina <reina@nsi.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can I CONSTRAIN a particular value to be UNIQUE?
Date: Thu, 29 Nov 2001 11:59:40 -0800 (PST)
Message-ID: <20011129115442.X45413-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <f40d3195.0111291101.3b2a5074@posting.google.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, reina@nsi.edu
Subject: Re: Can I CONSTRAIN a particular value to be UNIQUE?
In-Reply-To: <20011129115442.X45413-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox