Postgres case-insensitive exclusion constraint

Question

I'm trying to create an exclusion constraint that prevents overlapping effective timestamp ranges, but only compares ranges across records with the same text value. I'd like the text value comparison to be case insensitive. I can use the = operator with a text field, but not with a citext field, and the ~~* operator is not commutative.

This is PostgreSQL 9.5 and the citext and btree_gist extensions are created on the database.

CREATE TABLE customer_product_categories (
  id serial PRIMARY KEY, 
  name text NOT NULL, 
  effective tstzrange DEFAULT '[-infinity,infinity]', 
  EXCLUDE USING gist (name WITH ~~*, effective WITH &&)
)

ERROR: operator ~~*(text,text) is not commutative

CREATE TABLE customer_product_categories (
  id serial PRIMARY KEY, 
  name citext NOT NULL, 
  effective tstzrange DEFAULT '[-infinity,infinity]', 
  EXCLUDE USING gist (name WITH =, effective WITH &&)
)

ERROR: operator =(citext,citext) is not a member of operator family "gist_text_ops"

• How is ILIKE not commutative?
• Is there a way to do what I'm trying to do, or am I on a fool's errand?

Answer 1

A-ha! I figured it out. I had to go old-school:

CREATE TABLE "customer_product_categories" (
  "id" serial PRIMARY KEY, 
  "name" text NOT NULL, 
  "effective" tstzrange DEFAULT '[-infinity,infinity]', 
  EXCLUDE USING gist (LOWER("name") WITH =, "effective" WITH &&)
)