Postgres 约束 [英] Postgres constraint

问题描述

在 postgres 中有没有办法创建一个像这样工作的约束:

Is there a way in postgres to create a constraint that works like so:

我有一个值为time_of_day"的实体.该值可以是早上、下午、晚上、白天、晚上或任何时候.

I have an entity that has a value "time_of_day". This value can either be morning, afternoon, evening, day, night or anytime.

所以我想弄清楚如何允许以下组合:

SO I am trying to figure out how to allow the following combinations:

1. 任何时候(不能有其他任何东西)，即如果选择了任何时间，则只能有一行
2. 早上或下午 - 可以是多行，但不能包含任何时间".也不能是相同类型的两行，例如两行早上".

(2) 已经完成，因为它只是 time_of_day 的标准唯一约束.我如何实现(1).可能吗?

(2) has been done, as it is just a standard unique constraint on time_of_day. How do I achieve (1). Is it possible?

推荐答案

那就是简单"因为 PostgreSQL 非常具有可扩展性.您可以定义自己的类型、类型的比较运算符以及与 btree 索引一起使用的运算符类，以便 PostgreSQL 知道如何比较它们.

That is “easy” because PostgreSQL is so extensible. You can define your own type, comparison operators for the type and an operator class to use with a btree index so that PostgreSQL knows how to compare them.

诀窍是定义相等"；以这种方式使冲突的值相等.

The trick is to define “equal” in such a way that conflicting values are equal.

首先，我们定义我们的类型:

First, we define our type:

CREATE TYPE tod AS ENUM ('morning', 'afternoon', 'anytime');

然后我们定义一个索引支持例程，以便btree索引知道如何比较值:

Then we define an index support routine so that the btree index knows how to compare the values:

CREATE FUNCTION tod_compare(tod, tod) RETURNS integer
   IMMUTABLE LANGUAGE sql AS
$$SELECT CASE WHEN $1 = 'morning' AND $2 = 'afternoon' THEN -1
            WHEN $1 = 'afternoon' AND $2 = 'morning' THEN 1
            ELSE 0
       END$$;

基于这个比较函数，我们定义了实现比较运算符的函数:

Based on this comparison function, we define functions that implement the comparison operators:

CREATE FUNCTION tod_eq(tod, tod) RETURNS boolean IMMUTABLE LANGUAGE sql
   AS 'SELECT tod_compare($1, $2) = 0';

CREATE FUNCTION tod_lt(tod, tod) RETURNS boolean IMMUTABLE LANGUAGE sql
   AS 'SELECT tod_compare($1, $2) = -1';

CREATE FUNCTION tod_le(tod, tod) RETURNS boolean IMMUTABLE LANGUAGE sql
   AS 'SELECT tod_compare($1, $2) <= 0';

CREATE FUNCTION tod_ge(tod, tod) RETURNS boolean IMMUTABLE LANGUAGE sql
   AS 'SELECT tod_compare($1, $2) >= 0';

CREATE FUNCTION tod_gt(tod, tod) RETURNS boolean IMMUTABLE LANGUAGE sql
   AS 'SELECT tod_compare($1, $2) = 1';

CREATE FUNCTION tod_ne(tod, tod) RETURNS boolean IMMUTABLE LANGUAGE sql
   AS 'SELECT tod_compare($1, $2) <> 0';

现在我们可以在我们的类型上定义运算符:

Now we can define operators on our type:

CREATE OPERATOR ~=~ (
   PROCEDURE = tod_eq,
   LEFTARG = tod,
   RIGHTARG = tod,
   COMMUTATOR = ~=~,
   NEGATOR = ~<>~
);

CREATE OPERATOR ~<>~ (
   PROCEDURE = tod_ne,
   LEFTARG = tod,
   RIGHTARG = tod,
   COMMUTATOR = ~<>~,
   NEGATOR = ~=~
);

CREATE OPERATOR ~<=~ (
   PROCEDURE = tod_le,
   LEFTARG = tod,
   RIGHTARG = tod,
   COMMUTATOR = ~>=~,
   NEGATOR = ~>~
); 

CREATE OPERATOR ~<~ (
   PROCEDURE = tod_lt,
   LEFTARG = tod,
   RIGHTARG = tod,
   COMMUTATOR = ~>~,
   NEGATOR = ~>=~
);

CREATE OPERATOR ~>~ (
   PROCEDURE = tod_gt,
   LEFTARG = tod,
   RIGHTARG = tod,
   COMMUTATOR = ~<~,
   NEGATOR = ~<=~
);

CREATE OPERATOR ~>=~ (
   PROCEDURE = tod_ge,
   LEFTARG = tod,
   RIGHTARG = tod,
   COMMUTATOR = ~<=~,
   NEGATOR = ~<~
);

现在剩下的就是定义一个可用于定义索引的操作符类(这需要超级用户权限):

Now all that is left is to define an operator class that can be used to define an index (this requires superuser privileges):

CREATE OPERATOR CLASS tod_ops DEFAULT FOR TYPE tod USING btree AS
   OPERATOR 1 ~<~(tod,tod),
   OPERATOR 2 ~<=~(tod,tod),
   OPERATOR 3 ~=~(tod,tod),
   OPERATOR 4 ~>=~(tod,tod),
   OPERATOR 5 ~>~(tod,tod),
   FUNCTION 1 tod_compare(tod,tod);

现在我们可以定义一个使用新数据类型的表.

Now we can define a table that uses the new data type.

由于我们将 tod_ops 定义为类型 tod 的默认操作符类，我们可以创建一个简单的唯一约束，底层索引将使用我们的操作符类.>

Since we defined tod_ops as the default operator class for type tod, we can create a simple unique constraint, and the underlying index will use our operator class.

CREATE TABLE schedule (
   id integer PRIMARY KEY,
   day date NOT NULL,
   time_of_day tod NOT NULL,
   UNIQUE (day, time_of_day)
);

让我们测试一下:

INSERT INTO schedule VALUES (1, '2018-05-01', 'morning');

INSERT INTO schedule VALUES (2, '2018-05-01', 'afternoon');

INSERT INTO schedule VALUES (3, '2018-05-02', 'anytime');

INSERT INTO schedule VALUES (4, '2018-05-02', 'morning');
ERROR:  duplicate key value violates unique constraint "schedule_day_time_of_day_key"
DETAIL:  Key (day, time_of_day)=(2018-05-02, morning) already exists.

PostgreSQL 是不是很酷?

Isn't PostgreSQL cool?

这篇关于Postgres 约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！