Re: Help with exclusion constraint

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)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