sql - 使用前缀的 PostgreSQL 约束

Question

假设我有以下 PostgreSQL 表:

id | key
---+--------
1  | 'a.b.c'

我需要防止使用作为另一个键前缀的键插入记录。例如，我应该能够插入:

• 'a.b.b'

但不应接受以下 key :

• 'a.b'
• 'a.b.c'
• 'a.b.c.d'

有没有办法实现这一点 - 通过约束或锁定机制(在插入​​之前检查是否存在)？

Answer 1

此解决方案基于 PostgreSQL user-defined operators和排除约束( base syntax ， more details )。

注意:更多测试表明此解决方案(目前)不起作用。见底部。

1. 创建一个 has_common_prefix(text,text) 函数，它将逻辑地计算出您需要的内容。将函数标记为 IMMUTABLE。

   CREATE OR REPLACE FUNCTION
   has_common_prefix(text,text)
   RETURNS boolean
   IMMUTABLE STRICT
   LANGUAGE SQL AS $$
     SELECT position ($1 in $2) = 1 OR position ($2 in $1) = 1
   $$;
   

2. 为索引创建一个运算符

   CREATE OPERATOR <~> (
     PROCEDURE = has_common_prefix,
     LEFTARG   = text,
     RIGHTARG  = text,
     COMMUTATOR = <~>
   );
   

3. 创建排除约束

   CREATE TABLE keys ( key text );
   
   ALTER TABLE keys
     ADD CONSTRAINT keys_cannot_have_common_prefix
     EXCLUDE ( key WITH <~> ); 
   

但是，最后一点会产生此错误:

    ERROR:  operator <~>(text,text) is not a member of operator family "text_ops"
    DETAIL:  The exclusion operator must be related to the index operator class for the constraint.

这是因为要创建索引，PostgreSQL 需要逻辑 运算符通过实体调用“运算符类”与物理 索引方法绑定(bind)。所以我们需要提供该逻辑:

CREATE OR REPLACE FUNCTION keycmp(text,text)
RETURNS integer IMMUTABLE STRICT
LANGUAGE SQL AS $$
  SELECT CASE
    WHEN $1 = $2 OR position ($1 in $2) = 1 OR position ($2 in $1) = 1 THEN 0
    WHEN $1 < $2 THEN -1
    ELSE 1
  END
$$;

CREATE OPERATOR CLASS key_ops FOR TYPE text USING btree AS
  OPERATOR 3 <~> (text, text),
  FUNCTION 1 keycmp (text, text)
;

ALTER TABLE keys
  ADD CONSTRAINT keys_cannot_have_common_prefix
  EXCLUDE ( key key_ops WITH <~> );

现在，它起作用了:

INSERT INTO keys SELECT 'ara';
INSERT 0 1
INSERT INTO keys SELECT 'arka';
INSERT 0 1
INSERT INTO keys SELECT 'barka';
INSERT 0 1
INSERT INTO keys SELECT 'arak';
psql:test.sql:44: ERROR:  conflicting key value violates exclusion constraint "keys_cannot_have_common_prefix"
DETAIL:  Key (key)=(arak) conflicts with existing key (key)=(ara).
INSERT INTO keys SELECT 'bark';
psql:test.sql:45: ERROR:  conflicting key value violates exclusion constraint "keys_cannot_have_common_prefix"
DETAIL:  Key (key)=(bark) conflicts with existing key (key)=(barka).

注意:更多测试表明此解决方案还不起作用:最后一个 INSERT 应该会失败。

INSERT INTO keys SELECT 'a';
INSERT 0 1
INSERT INTO keys SELECT 'ac';
ERROR:  conflicting key value violates exclusion constraint "keys_cannot_have_common_prefix"
DETAIL:  Key (key)=(ac) conflicts with existing key (key)=(a).
INSERT INTO keys SELECT 'ab';
INSERT 0 1