If you use NULL instead of 0 for the value of success, then there is no
problem:
est=# create table x (a int4, b int4, unique(a,b));
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'x_a_key' for table
'x'
CREATE
test=# insert into x values (1, NULL);
INSERT 2829459 1
test=# insert into x values (1, NULL);
INSERT 2829460 1
test=# insert into x values (1, NULL);
INSERT 2829461 1
test=# select * from x;a | b
---+---1 |1 |1 |
(3 rows)
Cheers,
Chris
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Tony Reina
> Sent: Friday, 30 November 2001 3:01 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Can I CONSTRAIN a particular value to be UNIQUE?
>
>
> 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?
>
> Thanks.
> -Tony
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>