Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fATJxem24670 for ; Thu, 29 Nov 2001 14:59:40 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id C3430D5EB; Thu, 29 Nov 2001 11:59:40 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id B8F4B5BED; Thu, 29 Nov 2001 11:59:40 -0800 (PST) Date: Thu, 29 Nov 2001 11:59:40 -0800 (PST) From: Stephan Szabo To: Tony Reina Cc: Subject: Re: Can I CONSTRAIN a particular value to be UNIQUE? In-Reply-To: Message-ID: <20011129115442.X45413-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/417 X-Sequence-Number: 5799 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.