Postgres不区分大小写的排除约束

Question

我试图创建一个排除约束，防止有效时间戳范围重叠，但只比较具有相同文本值的记录的范围。我希望文本值比较不区分大小写。我可以将=运算符与文本字段一起使用，但不能与citext字段一起使用，~*运算符是不可交换的。

这是PostgreSQL 9.5，在数据库上创建了citext和btree_gist扩展。

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"

• i类如何不可交换
• 有没有办法做我想做的事，还是我在做傻事

Answer 1

啊哈！我想出来了。我不得不走老派：

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