如何使PostgreSQL约束只适用于一个新值

Question

我是PostgreSQL的新手，非常喜欢约束如何与行级安全一起工作，但是我很困惑如何使它们做我想要的。

我有一个列，我想添加一个约束，为文本列创建一个最小长度，这个检查适用于:

(length((column_name):: text) > 6)

但是，它也会阻止用户更新任何column_name已经少于6个字符的行。

我想这样做，所以他们不能改变那个值，但仍然可以更新一行已经发生了，所以他们可以根据我的新策略根据需要改变它。

这可能吗?

Answer 1

但是，它也会阻止用户更新任何column_name已经少于6个字符的行。

嗯,没有。当您尝试添加CHECK约束时，将检查所有现有行，如果发现任何违反将引发异常。
你必须让它成为NOT VALID。是的。

你真的需要一个触发器检查新值的INSERT或UPDATE。没有那么便宜，也没有那么防弹，但仍然很结实。如:

CREATE OR REPLACE FUNCTION trg_col_min_len6()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
IF TG_OP = 'UPDATE'
AND OLD.column_name IS NOT DISTINCT FROM NEW.column_name THEN
-- do nothing
ELSE
RAISE EXCEPTION 'New value for column "note" must have at least 6 characters.';
END IF;

RETURN NEW;
END
$func$;
-- trigger
CREATE TRIGGER tbl1_column_name_min_len6
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW
WHEN (length(NEW.column_name) < 7)
EXECUTE FUNCTION trg_col_min_len6();

db<此处小提琴>

直接向触发器检入WHEN条件应该是最有效的。然后，触发器函数只在短值时调用，并且可以非常简单。
:

• 触发多个WHEN条件
• 更新列a或列b或列c时触发火灾

Answer 2

您可以为Insert和Update创建单独的触发器，让每个触发器完全定义何时触发。如果DML操作需要完全不同的逻辑，这种技术允许编写专用的触发器函数。在这种情况下，不需要触发函数减少到raise exception ...。看到演示

-- Single trigger function for both Insert and Delete
create or replace function trg_col_min_len6()
returns trigger
language plpgsql 
as $$
begin
raise exception 'Cannot % val = ''%''. Must have at least 6 characters.'
, tg_op, new.val;
return null;
end;
$$;
-- trigger before insert 
create trigger tbl_val_min_len6_bir
before insert 
on tbl
for each row
when (length(new.val) < 6)
execute function trg_col_min_len6();
-- trugger before update 
create trigger tbl_val_min_len6_bur
before update
on tbl
for each row    
when (    length(new.val) < 6
and new.val is distinct from old.val 
) 
execute function trg_col_min_len6();