This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: pgsql-general-postgresql.org@localhost.postgresql.org
Date: Sat, 02 Nov 2002 09:36:45 -0800
From: Ron St-Pierre <rstpierre@syscor.com>
Subject: [GENERAL] Constraint Problem
how to implement it. I have an intermediate table with a compound PK and
a boolean 'ysnDefault' column:
comanyID INTEGER REFERENCES companies,
assocationID INTEGER REFERENCES associations,
ysnDefault BOOLEAN
I just want to constrain the ysnDefault field to only allow one TRUE
value for any companyID/associationID pair, with no restrictions on the
number of FALSES.
At first glance I thought it would be easy, but I can't see how to do
it. Any suggestions?
===
To: pgsql-general-postgresql.org@localhost.postgresql.org
Date: Sun, 02 Nov 2003 12:48:15 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [GENERAL] Constraint Problem
Date: Sun, 02 Nov 2003 12:48:15 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
Ron St-Pierre <rstpierre@syscor.com> writes:
> I just want to constrain the ysnDefault field to only allow one TRUE
> value for any companyID/associationID pair, with no restrictions on the
> number of FALSES.
You could do that with a partial unique index. There is an example
at the bottom of
http://www.postgresql.org/docs/7.3/static/indexes-partial.html
===
To: pgsql-general-postgresql.org@localhost.postgresql.org
Date: Mon, 03 Nov 2003 10:35:22 -0800
From: Ron St-Pierre <rstpierre@syscor.com>
Subject: Re: [GENERAL] Constraint Problem
>>I just want to constrain the ysnDefault field to only allow one TRUE
>>value for any companyID/associationID pair, with no restrictions on the
>>number of FALSES.
>You could do that with a partial unique index. There is an example
>at the bottom of
>http://www.postgresql.org/docs/7.3/static/indexes-partial.html
This is not quite what I need. I need to create a constraint to allow
only -one- of
company<->association<->default(=true) value
but any number of
company<->association<->default(=false) values
I've tried many different ALTER TABLE ... CREATE CONSTRAINT variations,
all without success.
Anyone know how?
===
To: pgsql-general-postgresql.org@localhost.postgresql.org
Date: Mon, 3 Nov 2003 13:00:25 -0700 (MST)
From: "scott.marlowe" <scott.marlowe@ihs.com>
Subject: Re: [GENERAL] Constraint Problem
Ron St-Pierre wrote:
> Tom Lane wrote:
> >Ron St-Pierre <rstpierre@syscor.com> writes:
> >>I just want to constrain the ysnDefault field to only
> >>allow one TRUE value for any companyID/associationID
> >>pair, with no restrictions on the number of FALSES.
> >You could do that with a partial unique index. There is an example
> >at the bottom of
> >http://www.postgresql.org/docs/7.3/static/indexes-partial.html
> This is not quite what I need. I need to create a constraint to allow
> only -one- of
> company<->association<->default(=true) value
> but any number of
> company<->association<->default(=false) values
>
> I've tried many different ALTER TABLE ... CREATE CONSTRAINT variations,
> all without success.
Wouldn't a unique constraint on those three fields do this? Nulls don't
violate unique constraints. Does that work, or did I miss too much of
this conversation?
===
To: pgsql-general-postgresql.org@localhost.postgresql.org
Date: Tue, 04 Nov 2003 08:39:13 -0800
From: Ron St-Pierre <rstpierre@syscor.com>
Subject: Re: [GENERAL] Constraint Problem
>>This is not quite what I need. I need to create a constraint to allow only
>>-one- of
>> company<->association<->default(=true) value
>>but any number of
>> company<->association<->default(=false) values
>So a unique index on "(company,association) where default" doesn't do what you
>want?
No it doesn't. For example, after I create the unique index I can still
input:
company10 association7 true
company10 association7 true
company10 association7 true
I want to prevent this from happening, but still allow multiple
company10 association7 false
company10 association7 false
entries for example.
The idea of using NULLs is a good idea, but this is a production
database and would require changes to the web-based front end (7 of
them), not to mention each database. That's why I want to change the
behaviour to only allow one unique company<-->association<-->TRUE
combination. Right now there are a number of companies which have
multiple default associations in the database, so I am going to have to
back-trace and find out which association is actally the correct default.
BTW I am using postgresql 7.3.4
===
To: pgsql-general-postgresql.org@localhost.postgresql.org
Date: 04 Nov 2003 17:59:21 +0100
From: Csaba Nagy <nagy@ecircle-ag.com>
Subject: Re: [GENERAL] Constraint Problem
Ron St-Pierre wrote:
> Greg Stark wrote:
> >Ron St-Pierre <rstpierre@syscor.com> writes:
> >>This is not quite what I need. I need to create a constraint to allow only
> >>-one- of
> >> company<->association<->default(=true) value
> >>but any number of
> >> company<->association<->default(=false) values
> >So a unique index on "(company,association) where default" doesn't do what you
> >want?
> No it doesn't. For example, after I create the unique index I can still
> input:
> company10 association7 true
> company10 association7 true
> company10 association7 true
> I want to prevent this from happening, but still allow multiple
> company10 association7 false
> company10 association7 false
> entries for example.
>
> The idea of using NULLs is a good idea, but this is a production
> database and would require changes to the web-based front end (7 of
> them), not to mention each database. That's why I want to change the
> behaviour to only allow one unique company<-->association<-->TRUE
> combination. Right now there are a number of companies which have
> multiple default associations in the database, so I am going to have to
> back-trace and find out which association is actally the correct default.
>
> BTW I am using postgresql 7.3.4
You probably didn't quite understand the suggestion. You should create a
unique index like:
create unique index your_index_name on your_table (companyID,
associationID) where ysnDefault;
This will restrict the uniqueness checks to the records where ysnDefault
is true (and not null, of course).
===
Date: Tue, 4 Nov 2003 09:40:28 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Ron St-Pierre <rstpierre@syscor.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Constraint Problem
Ron St-Pierre wrote:
> Greg Stark wrote:
>
> >Ron St-Pierre <rstpierre@syscor.com> writes:
> >>This is not quite what I need. I need to create a constraint to allow only
> >>-one- of
> >> company<->association<->default(=true) value
> >>but any number of
> >> company<->association<->default(=false) values
> >So a unique index on "(company,association) where default" doesn't do what you
> >want?
> >
> No it doesn't. For example, after I create the unique index I can still
> input:
> company10 association7 true
> company10 association7 true
> company10 association7 true
You shouldn't be able to and I can't replicate similar behavior in a
simple test on 7.3.4. I get "Cannot insert a duplicate key into unique
index" errors.
create table a(a int, b int, c boolean);
create unique index a_ind on a(a,b) where c;
insert into a values (1,1,true);
insert into a values (1,1,true);
insert into a values (1,1,false);
insert into a values (1,1,false);
insert into a values (1,2,true);
Where the second insert fails, but the others succeed.
===
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Constraint Problem
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 04 Nov 2003 12:45:57 -0500
Ron St-Pierre <rstpierre@syscor.com> writes:
> No it doesn't. For example, after I create the unique index I can still input:
> company10 association7 true
> company10 association7 true
> company10 association7 true
> I want to prevent this from happening, but still allow multiple
> company10 association7 false
> company10 association7 false
> entries for example.
For example:
test=# create table test (company integer, association integer, isdefault boolean);
CREATE TABLE
test=# create unique index testi on (company,association) where isdefault;
ERROR: syntax error at or near "(" at character 30
test=# create unique index testi on test (company,association) where isdefault;
CREATE INDEX
test=# insert into test values (10,7,true);
INSERT 6888594 1
test=# insert into test values (10,7,true);
ERROR: duplicate key violates unique constraint "testi"
test=# insert into test values (10,7,false);
INSERT 6888596 1
test=# insert into test values (10,7,false);
INSERT 6888597 1
test=# select * from test;
company | association | isdefault
---------+-------------+-----------
10 | 7 | t
10 | 7 | f
10 | 7 | f
(3 rows)
===
Date: Tue, 04 Nov 2003 10:25:03 -0800
From: Ron St-Pierre <rstpierre@syscor.com>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Constraint Problem
Sender: pgsql-general-owner@postgresql.org
Stephan Szabo wrote:
> Ron St-Pierre wrote:
>>Greg Stark wrote:
>>>Ron St-Pierre <rstpierre@syscor.com> writes:
>>>>This is not quite what I need. I need to create a constraint to allow only
>>>>-one- of
>>>> company<->association<->default(=true) value
>>>>but any number of
>>>> company<->association<->default(=false) values
>>>So a unique index on "(company,association) where default" doesn't do what you
>>>want?
>>No it doesn't. For example, after I create the unique index I can still
>>input:
>> company10 association7 true
>> company10 association7 true
>> company10 association7 true
>You shouldn't be able to and I can't replicate similar behavior in a
>simple test on 7.3.4. I get "Cannot insert a duplicate key into unique
>index" errors.
>
>create table a(a int, b int, c boolean);
>create unique index a_ind on a(a,b) where c;
>insert into a values (1,1,true);
>insert into a values (1,1,true);
>insert into a values (1,1,false);
>insert into a values (1,1,false);
>insert into a values (1,2,true);
>
>Where the second insert fails, but the others succeed.
You're right. When I run this the second insert fails. However, I
modified my table to add the index and then successfully added an entry
which should have failed. So I created a new table:
CREATE TABLE compass (
compassnID SERIAL PRIMARY KEY,
company int4 NOT NULL REFERENCES tblcompanies (cntcompanyid),
association int4 NOT NULL REFERENCES tblassociations
(cntasncode),
ysnDefault bool
);
CREATE UNIQUE INDEX compassoc_default_ind ON compass
(company,association) WHERE ysnDefault;
and then inserted the data as:
INSERT INTO compass(company, association, ysnDefault) SELECT
company, association, ysnDefault FROM oldCompAss;
I then checked::
planrrontest=# SELECT * FROM compass WHERE company=23590;
compassnid | company | association | ysndefault
------------+---------+-------------+------------
7777 | 23590 | 4 | t
8038 | 23590 | 2 | t
8040 | 23590 | 7 | t
(3 rows)
And as you can see company 23590 has three default associations. Any
ideas on how I can get around this?
===
Date: Tue, 4 Nov 2003 16:03:17 -0300
From: Alvaro Herrera <alvherre@dcc.uchile.cl>
To: Ron St-Pierre <rstpierre@syscor.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Constraint Problem
Ron St-Pierre wrote:
> Stephan Szabo wrote:
> CREATE TABLE compass (
> compassnID SERIAL PRIMARY KEY,
> company int4 NOT NULL REFERENCES tblcompanies (cntcompanyid),
> association int4 NOT NULL REFERENCES tblassociations
> (cntasncode),
> ysnDefault bool
> );
>
> CREATE UNIQUE INDEX compassoc_default_ind ON compass
> (company,association) WHERE ysnDefault;
>
> And as you can see company 23590 has three default associations. Any
> ideas on how I can get around this?
Well, shouldn't the index be
CREATE UNIQUE INDEX compassoc_default_ind ON compass (company) WHERE ysnDefault;
?
===
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Constraint Problem
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 04 Nov 2003 14:10:33 -0500
Ron St-Pierre <rstpierre@syscor.com> writes:
>>>>>This is not quite what I need. I need to create a constraint to allow only
>>>>>-one- of
>>>>> company<->association<->default(=true) value
>>>>>but any number of
>>>>> company<->association<->default(=false) values
> I then checked::
> planrrontest=# SELECT * FROM compass WHERE company=23590;
> compassnid | company | association | ysndefault
> ------------+---------+-------------+------------
> 7777 | 23590 | 4 | t
> 8038 | 23590 | 2 | t
> 8040 | 23590 | 7 | t
> (3 rows)
>
> And as you can see company 23590 has three default associations. Any ideas on
> how I can get around this?
But they're all different associations. If you reread your original question
above you'll see that's not what you described. You said you only wanted one
of any <company,association,default> value when default is true.
Try just
create unique index on compass (company) where ysndefault
===
Date: Tue, 04 Nov 2003 14:15:00 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Constraint Problem
Ron St-Pierre wrote:
> Stephan Szabo wrote:
>
>>Ron St-Pierre wrote:
>>>Greg Stark wrote:
>>>>Ron St-Pierre <rstpierre@syscor.com> writes:
>>>>>This is not quite what I need. I need to create a constraint to allow only
>>>>>-one- of
>>>>> company<->association<->default(=true) value
>>>>>but any number of
>>>>> company<->association<->default(=false) values
>>>>So a unique index on "(company,association) where default" doesn't do what you
>>>>want?
>>>No it doesn't. For example, after I create the unique index I can still
>>>input:
>>> company10 association7 true
>>> company10 association7 true
>>> company10 association7 true
>>You shouldn't be able to and I can't replicate similar behavior in a
>>simple test on 7.3.4. I get "Cannot insert a duplicate key into unique
>>index" errors.
>>
>>create table a(a int, b int, c boolean);
>>create unique index a_ind on a(a,b) where c;
>>insert into a values (1,1,true);
>>insert into a values (1,1,true);
>>insert into a values (1,1,false);
>>insert into a values (1,1,false);
>>insert into a values (1,2,true);
>>
>>Where the second insert fails, but the others succeed.
> You're right. When I run this the second insert fails. However, I
> modified my table to add the index and then successfully added an entry
> which should have failed. So I created a new table:
> CREATE TABLE compass (
> compassnID SERIAL PRIMARY KEY,
> company int4 NOT NULL REFERENCES tblcompanies (cntcompanyid),
> association int4 NOT NULL REFERENCES tblassociations
> (cntasncode),
> ysnDefault bool
> );
>
> CREATE UNIQUE INDEX compassoc_default_ind ON compass
> (company,association) WHERE ysnDefault;
>
> and then inserted the data as:
> INSERT INTO compass(company, association, ysnDefault) SELECT
> company, association, ysnDefault FROM oldCompAss;
>
> I then checked::
> planrrontest=# SELECT * FROM compass WHERE company=23590;
> compassnid | company | association | ysndefault
> ------------+---------+-------------+------------
> 7777 | 23590 | 4 | t
> 8038 | 23590 | 2 | t
> 8040 | 23590 | 7 | t
> (3 rows)
> And as you can see company 23590 has three default associations. Any
> ideas on how I can get around this?
That's what you complained about. I see different associations.
I guess you want only one row with ysndefault=true "per company", not
"per company+association". So the unique index has to be
CREATE UNIQUE INDEX bla ON compass (company) WHERE ysnDefault;
===
Date: Tue, 04 Nov 2003 11:30:45 -0800
From: Ron St-Pierre <rstpierre@syscor.com>
To: pgsql-general <pgsql-general@postgresql.org>
Cc: Greg Stark <gsstark@mit.edu>
Subject: Re: [GENERAL] Constraint Problem
Greg Stark wrote:
>Ron St-Pierre <rstpierre@syscor.com> writes:
>>>>>>This is not quite what I need. I need to create a constraint to allow only
>>>>>>-one- of
>>>>>> company<->association<->default(=true) value
>>>>>>but any number of
>>>>>> company<->association<->default(=false) values
>>I then checked::
>> planrrontest=# SELECT * FROM compass WHERE company=23590;
>> compassnid | company | association | ysndefault
>> ------------+---------+-------------+------------
>> 7777 | 23590 | 4 | t
>> 8038 | 23590 | 2 | t
>> 8040 | 23590 | 7 | t
>> (3 rows)
>>
>>And as you can see company 23590 has three default associations. Any ideas on
>>how I can get around this?
>But they're all different associations. If you reread your original question
>above you'll see that's not what you described. You said you only wanted one
>of any <company,association,default> value when default is true.
>
>Try just
>
>create unique index on compass (company) where ysndefault
You're right, I was trying to associate the company-association-default
where default=TRUE, when I didn't need the association as part of it at
all. It's working now, thanks. I'll now try to find my way out of
'Monday morning mode'.
===
To: pgsql-general <pgsql-general@postgresql.org>, Greg Stark <gsstark@mit.edu>
Subject: Re: [GENERAL] Constraint Problem
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 04 Nov 2003 14:59:40 -0500
Ron St-Pierre <rstpierre@syscor.com> writes:
> Greg Stark wrote:
>
> >Try just
> >
> >create unique index on compass (company) where ysndefault
You might also want
create unique index on compass (company,association)
If a company isn't supposed to have duplicate associations at all. This would
also prevent having a default and non-default relationship to the same
association.
===