Re: [GENERAL] Help with exclusion constraint

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Moshe Jacobson
Sent: Friday, March 28, 2014 10:31 AM
To: pgsql-general
Subject: [GENERAL] Help with exclusion constraint

Take the following table:
CREATE TABLE exclusion_example AS
(
 pk_col  integer primary key,
 fk_col integer not null references other_table,
 bool_col boolean not null
);
I want to ensure that for any given value of fk_col that there is a maximum of 
one row with bool_col = true. I wanted to write an exclusion constraint such as 
this:
alter table exclusion_example add exclude using btree ( fk_col with = , 
bool_col with and );
..
..
..
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc.
2323 Cumberland Parkway * Suite 201 * Atlanta, GA 30339
"Quality is not an act, it is a habit." - Aristotle 

For this: 

"any given value of fk_col that there is a maximum of one row with bool_col = 
true."

why don't you (instead) create partial unique index:

CREATE UNIQUE INDEX on exclusion_example(fk_col, bool_col) WHERE bool_col IS 
TRUE;

Regards,
Igor Neyman


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Help with exclusion constraint

[Moshe Jacobson]

Take the following table:

CREATE TABLE exclusion_example AS
(
 pk_col  integer primary key,
 fk_col integer not null references other_table,
 bool_col boolean not null
);

I want to ensure that for any given value of fk_col that there is a maximum
of one row with bool_col = true. I wanted to write an exclusion constraint
such as this:

alter table exclusion_example add exclude using btree ( fk_col with =
, bool_col with and );

But this doesn't work because "and" is not an operator.
So I created my own operator &&(bool, bool):

create function fn_boolean_and( bool, bool ) returns bool as
 $_$
select $1 and $2;
 $_$
language sql stable strict;
create operator &&
(
procedure = fn_boolean_and(bool, bool),
leftarg = bool,
rightarg = bool,
commutator = &&
);

But now when I try to create the exclusion constraint, I get the following:

mydb=# alter table exclusion_example add exclude using btree ( fk_col
with = , bool_col with and );
ERROR:  operator &&(boolean,boolean) is not a member of operator
family "bool_ops"
DETAIL:  The exclusion operator must be related to the index operator
class for the constraint.

I'm not sure what to do about this. Any help would be appreciated.

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. 
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Help with exclusion constraint

[Moshe Jacobson]

On Fri, Mar 28, 2014 at 12:21 PM, Igor Neyman wrote:

> For this:
> "any given value of fk_col that there is a maximum of one row with
> bool_col = true."
>
> why don't you (instead) create partial unique index:
> CREATE UNIQUE INDEX on exclusion_example(fk_col, bool_col) WHERE bool_col
> IS TRUE;
>

Ahh yes, why didn't I think of that? Thank you.


Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. 
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Help with exclusion constraint

[Moshe Jacobson]

On Fri, Mar 28, 2014 at 12:45 PM,  wrote:

> > I want to ensure that for any given value of fk_col that there is a
> maximum
> > of one row with bool_col = true.
>
> This should be what you want:
>
> ALTER TABLE exclusion_example
> ADD CONSTRAINT ex
> EXCLUDE (fk_col WITH =) WHERE (bool_col);


Yes, that would do the trick!
I think I'll just set up a partial unique index as per Igor's suggestion,
however.

Thank you!

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. 
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Help with exclusion constraint

[hari . fuchs]

Moshe Jacobson  writes:

> Take the following table:
>
> CREATE TABLE exclusion_example AS
> (
>  pk_col  integer primary key,
>  fk_col integer not null references other_table,
>  bool_col boolean not null
> );
>
> I want to ensure that for any given value of fk_col that there is a maximum
> of one row with bool_col = true.

This should be what you want:

ALTER TABLE exclusion_example
ADD CONSTRAINT ex
EXCLUDE (fk_col WITH =) WHERE (bool_col);



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general