postgresql 排它约束

--pg支持 EXCLUSION Constraint,排它约束是约束中定义的操作计算结果为false，则不允许插入
Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

--排它约束会自动建立一个索引，且为gist索引
Exclusion constraints are implemented using an index，The access method must support amgettuple; at present this means GIN cannot be used. 
Although it’s allowed, there is little point in using B-tree or hash indexes with an exclusion constraint, because this does nothing that an ordinary unique constraint doesn’t do better. 
So in practice the access method will always be GiST or SP-GiST

--引入btree_gist扩展
postgres=#  CREATE EXTENSION btree_gist;
CREATE EXTENSION

--否则创建表时会报错
ERROR:  data type text 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.

--创建测试表
CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT ,
   AGE            INT   ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =, --如果满足name相同，age不相同则pg允许插入，否则不允许插入
   AGE WITH <>)  --其比较的结果是如果整个表边式返回true，则不允许插入，否则允许
);
  --插入测试数据
 INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
 --此条数据的name与第一条相同，且age与第一条也相同，故满足插入条件
 INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
 INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
 
 --此数据与上面数据的name相同，但age不相同，故不允许插入
postgres=# INSERT INTO COMPANY7 VALUES(2, 'Paul', 33, 'Texas', 20000.00 );       
ERROR:  duplicate key value violates unique constraint "company7_pkey"
DETAIL:  Key (id)=(2) already exists.

--查询数据库中在排序约束中可以使用的操作符
SELECT am.amname AS index_method,
opf.opfname AS opfamily_name,
amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid AND
amop.amopfamily = opf.oid and amname like 'gist'
ORDER BY index_method, opfamily_name, opfamily_operator;