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 leas

--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,'Texas',20000.00 ); INSERT INTO COMPANY7 VALUES(3,'Allen',42,20000.00 );  --此数据与上面数据的name相同，但age不相同，故不允许插入postgres=# INSERT INTO COMPANY7 VALUES(2,33,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_operatorFROM pg_am am,pg_opfamily opf,pg_amop amopWHERE opf.opfmethod = am.oID ANDamop.amopfamily = opf.oID and amname like 'gist'ORDER BY index_method,opfamily_name,opfamily_operator;

总结

以上是内存溢出为你收集整理的postgresql 排它约束全部内容，希望文章能够帮你解决postgresql 排它约束所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。