作者
digoal
日期
2019-05-27
标签
PostgreSQL , gist , exclude , 约束 , 一对一
背景
在应用约束中，有些时候需要限定两个属性的关系是一对一的，即一个字段的值确定后，另一个字段的值也相当于固定了。
这种情况可以使用exclude约束来搞定。
exclude的说明参考
https://www.postgresql.org/docs/12/sql-createtable.html
例子
```
=> CREATE TABLE zoo (
cage INTEGER,
animal TEXT,
EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'lion');
ERROR: conflicting key value violates exclusion constraint "zoo_cage_animal_excl"
DETAIL: Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).
=> INSERT INTO zoo VALUES(124, 'lion');
INSERT 0 1
```
以上例子，当cage=123固定之后，animal的值也固定了。
除此以外，exclude约束还可以用于几何类型，GIS类型的排他约束，例如地图中的多边形，不能有相交，存入一个多边形时，必须保证它和已有记录中的多边形不相交。
CREATE TABLE circles (
c circle,
EXCLUDE USING gist (c WITH &&)
);
exclude约束使用索引结构来保证强一致，不存在并发问题。性能也OK。
``` postgres=# \d+ circles Table "public.circles" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------+-----------+----------+---------+---------+--------------+------------- c | circle | | | | plain | | Indexes: "circles_c_excl" EXCLUDE USING gist (c WITH &&) Access method: heap
postgres=# \d+ circles_c_excl Index "public.circles_c_excl" Column | Type | Key? | Definition | Storage | Stats target --------+------+------+------------+---------+-------------- c | box | yes | c | plain | gist, for table "public.circles" ```
exclude约束是unique, pk以外的一种约束，是PG的扩展功能。
参考
https://www.postgresql.org/docs/12/btree-gist.html
https://www.postgresql.org/docs/12/sql-createtable.html
《会议室预定系统实践(解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
