From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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
towrite 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
