Postgres:添加约束(如果尚不存在) [英] Postgres: Add constraint if it doesn't already exist
问题描述
Postgres有什么办法说 ALTER TABLE foo ADD CONSTRAINT bar ...
如果约束已经存在,它将忽略该命令,因此它不会
Does Postgres have any way to say ALTER TABLE foo ADD CONSTRAINT bar ...
which will just ignore the command if the constraint already exists, so that it doesn't raise an error?
推荐答案
这可能有帮助,尽管可能有点脏:
This might help, although it may be a bit of a dirty hack:
create or replace function create_constraint_if_not_exists (
t_name text, c_name text, constraint_sql text
)
returns void AS
$$
begin
-- Look for our constraint
if not exists (select constraint_name
from information_schema.constraint_column_usage
where table_name = t_name and constraint_name = c_name) then
execute constraint_sql;
end if;
end;
$$ language 'plpgsql'
然后调用:
SELECT create_constraint_if_not_exists(
'foo',
'bar',
'ALTER TABLE foo ADD CONSTRAINT bar CHECK (foobies < 100);')
已更新:
根据 Webmut的回答如下所示:
ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
ALTER TABLE foo ADD CONSTRAINT bar ...;
在您的开发数据库中可能还不错,或者您知道可以关闭依赖于此的应用程序该数据库用于维护窗口。
That's probably fine in your development database, or where you know you can shut out the apps that depend on this database for a maintenance window.
但是,如果这是一个关键任务的24x7全天候生产环境,那么您真的不想像这样随意地放弃约束。即使是几毫秒,也会有一小段窗口,您不再需要执行约束,这可能会导致错误值漏掉。这可能会带来意想不到的后果,从而在将来的某个时候导致可观的业务成本。
But if this is a lively mission critical 24x7 production environment you don't really want to be dropping constraints willy nilly like this. Even for a few milliseconds there's a short window where you're no longer enforcing your constraint which may allow errant values to slip through. That may have unintended consequences leading to considerable business costs at some point down the road.
这篇关于Postgres:添加约束(如果尚不存在)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!