创建检查约束时如何替换变量

Question

我需要为新添加的行添加一个必填字段。但是，由于表的大小较大，因此不希望为旧行设置默认值。我需要提供一个自动化的脚本来实现这一点。我试过这个，但不起作用：

do $$
declare 
max_id int8;
begin
select max(id) into max_id from transactions;
alter table transactions add constraint check_process_is_assigned check (id <= max_id or process_id is not null);
end $$;

Answer 1

像ALTER TABLE这样的实用程序命令不接受参数。只有基本的DML命令SELECT、INSERT、UPDATE、DELETE可以执行。参见：

• 集合"；有效直到"；具有计算时间戳的值
• "错误：plpgsql中的"EXECUTE..UUSING..；"语句中没有参数$1">
• 从匿名块中的变量创建具有密码的用户

您需要动态SQL，如：

DO
$do$
BEGIN
EXECUTE format(
'ALTER TABLE transactions
ADD CONSTRAINT check_process_is_assigned CHECK (id <= %s OR process_id IS NOT NULL)'
, (SELECT max(id) FROM transactions)
);
END
$do$;

db<gt；小提琴这里

这将基于当前最大id创建CHECK约束。

也许NOT VALID约束会更好？未对照现有行检查：

ALTER TABLE transactions
ADD CONSTRAINT check_process_is_assigned CHECK (process_id IS NOT NULL) NOT VALID;

但你必须"；"修复"；在本例中更新的旧行。(例如，如果process_id到目前为止为NULL，则为其指定一个值。(请参阅：

• 对一组列强制NOT NULL，只对新行强制CHECK约束
• 在大表中填充新列的最佳方式