PostgreSQL exclude排它约束详解

pg中的exclude排它约束是用来保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回否或空值。
例如当我们表中某两列值要求是一对一的，即其中一列的值确定后，另一列的值也确定下来了，则可以使用exclude约束。

语法为：

CREATE TABLE circles 
( c circle,
EXCLUDE USING gist (c WITH &&)
);

其中需要注意的是：
增加一个排他约束将在约束声明所指定的类型上自动创建索引。

例子：
使用exclude约束前需要先创建btree_gist扩展，原因后面说明。

bill@bill=>create extension btree_gist ;
CREATE EXTENSION

创建排它约束的表：

bill@bill=> CREATE TABLE t2 (  
bill(#   c1   INTEGER,  
bill(#   c2 TEXT,  
bill(#   EXCLUDE USING GIST (c1 WITH =, c2 WITH <>)  
bill(# );  
CREATE TABLE

插入数据：

bill@bill=>insert into t2 values(1,'a');
INSERT 0 1
bill@bill=>insert into t2 values(1,'a');
INSERT 0 1
bill@bill=>insert into t2 values(1,'b');
ERROR:  conflicting key value violates exclusion constraint "t2_c1_c2_excl"
DETAIL:  Key (c1, c2)=(1, b) conflicts with existing key (c1, c2)=(1, a).

可以看到当c1列的值确定为1时，只能插入c2为’a’的值，插入其它数据则会报错。

除此以外，exclude约束还可以用于几何类型，GIS类型的排他约束，例如地图中的多边形，不能有相交，存入一个多边形时，必须保证它和已有记录中的多边形不相交。

CREATE TABLE circles (  
    c circle,  
    EXCLUDE USING gist (c WITH &&)  
);  

这里简单说明下为什么我们需要先安装btree_gist插件：
因为排除约束使用一个索引实现，这样每一个指定的操作符必须与用于索引访问方法index_method的一个适当的操作符类相关联。
访问方法必须支持amgettuple，目前这意味着GIN无法使用。而对于btree或者hash索引，尽管允许，但是在一个排除约束中使用 B-树或哈希索引没有意义，因为它无法做得比一个普通唯一 索引更出色。因此在实践中访问方法将总是GiST或SP-GiST。