CODEWITHSUNDEEP
postgresqldatabase-designconstraints
mwp
mwp

Reputation: 8467

Postgres case-insensitive exclusion constraint

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"

Upvotes: 3

Views: 848

Answers (1)

mwp
mwp

Reputation: 8467

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 &&)
)

Upvotes: 3

Related Questions