How can I use a Postgres EXCLUDE constraint to prevent inserting two primary rows?

Question

Postgres 9.0 supports exclusion constraints, which are kind of like a general unique constraint (http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE).

I figured that this might be a good way to solve this problem, but I can't figure out how to use the exclusion constraints properly.

Here's the problem: I have a table like this:

 CREATE TABLE emails (
     id integer NOT NULL,
     email character varying(255),
     "primary" boolean DEFAULT false,
     user_id integer,
 );

I want to ensure that only one row per unique user_id has "primary" equal to true. I tried to use an exclusion constraint like this:

 ALTER TABLE emails ADD CONSTRAINT one_primary_email_per_user EXCLUDE USING gist (user_id WITH =, "primary" WITH &);

Postgres refused that with:

 ERROR:  data type boolean has no default operator class for access method "gist"
 HINT:  You must specify an operator class for the index or define a default operator class for the data type.

I tried again casting the boolean column to bit:

 ALTER TABLE emails ADD CONSTRAINT one_primary_email_per_user EXCLUDE  (user_id WITH =, (case "primary" when 't' then '1'::bit else '0'::bit end) WITH &);

That didn't work. It seems &(bit,bit) is not part of the operator class bit_ops:

 ERROR:  operator &(bit,bit) is not a member of operator family "bit_ops"
 DETAIL:  The exclusion operator must be related to the index operator class for the constraint.

Looking at http://www.leadum.com/downloads/dbscribe/samples/postgresql/web_modern/opclass/main/790197862.html, it seems like bit_ops only includes ordering comparison operators (>,<,=,>=,<=) and not bitwise operators. I'm not really sure why that's the case.

Is this kind of exclusion possible with an EXCLUDE constraint? Is there a better way to do this?

Thanks for the help!

Answer 1

It should be simpler to create a partial unique index for that:

create unique index on emails(email) where (primary);

(Orthogonal to this answer, but since you're asking about the error message: for the exclude constraint, you'd want to add the btree_gin or btree_gist extensions for use with btree operators.)