Обсуждение: 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

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.

Stephan Szabo wrote:

> 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.

Yes, I was hoping that something like CHECK or perhaps a RULE would work,
but it appears that the easiest way may be using an external program.

Thanks Stephan.
-Tony

Tom Lane wrote:

> 2. In PG 7.2, partial indexes work again, so you could do
>
>         CREATE UNIQUE INDEX ... (subject, target, trial) WHERE success = 1;
>
> Which of these is better depends on how many of each sort of row
> you expect to have, and whether you'll be doing any queries wherein
> you could use an index on subject/target/trial for the non-success
> rows.
>

Oooooo! Option #2 seems very nice for what I'm trying to do. Would there be
any problem with me using

CREATE UNIQUE INDEX pk1 ON table1 (subject, target, trial) WHERE success = 1;
CREATE UNIQUE INDEX pk0 ON table1 (subject, target, trial) WHERE success = 0;

i.e. 2 partial indicies on the same table?


I know PG7.2 is still in beta, but it might be worthwhile for me to make the
jump from 7.1.3 for this feature. If I upgrade to the beta, will I have to do
a dump/restore again when the official 7.2 release comes out? I know I'll have
to do one for the beta anyway, but I'd hate to have to do it twice (especially
since the 7.2 official release will probably come out soon).

Thanks Tom.
-Tony

reina@nsi.edu (Tony Reina) writes:
> 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.

Two possibilities:

1. If you're not wedded to that particular data representation, consider  making the success values 1 and NULL not 1
and0.  Then a unique  index across all four columns would act as you desire.
 

2. In PG 7.2, partial indexes work again, so you could do
CREATE UNIQUE INDEX ... (subject, target, trial) WHERE success = 1;

Which of these is better depends on how many of each sort of row
you expect to have, and whether you'll be doing any queries wherein
you could use an index on subject/target/trial for the non-success
rows.
        regards, tom lane

"G. Anthony Reina" <reina@nsi.edu> writes:
> Oooooo! Option #2 seems very nice for what I'm trying to do. Would there be
> any problem with me using

> CREATE UNIQUE INDEX pk1 ON table1 (subject, target, trial) WHERE success = 1;
> CREATE UNIQUE INDEX pk0 ON table1 (subject, target, trial) WHERE success = 0;

> i.e. 2 partial indicies on the same table?

Not at all, but if those are the only two possible values of "success"
then it would seem this is the same as one non-partial unique index.
If you're thinking of allowing other, unconstrained success values then
this could make sense.

> I know PG7.2 is still in beta, but it might be worthwhile for me to make the
> jump from 7.1.3 for this feature. If I upgrade to the beta, will I have to do
> a dump/restore again when the official 7.2 release comes out?

We try to avoid forcing initdb after we start the beta cycle, but we
make no guarantees until we go final.  FWIW, at this point I doubt we'll
need to do that.
        regards, tom lane

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
>