幂等PostgreSQL DDL脚本 [英] Idempotent PostgreSQL DDL scripts

问题描述

我正在寻找一种以幂等方式编写postgreSQL模式更改脚本的方法.

在MSSQL中，我可以执行以下操作:

if(not exists(select * from information_schema.columns where table_name = 'x' and column_name = 'y'))
begin
    alter table x add y int
end
go

PostgreSQL似乎不像MSSQL在T-SQL中那样允许即席pl/pgsql，因此我不能在SQL脚本中使用控制结构并通过psql -f x.sql运行它. /p>

我知道PostgreSQL如果对象已经存在会抛出错误，但是我不想忽略错误.

我可以使用一些模式版本控制技术，例如dbdeploy，但我真的很喜欢通过psql运行一组文件而不会引起任何不良副作用的简单性.

这可能吗?

谢谢， 标记

解决方案

您可能会发现此博客文章对您有所帮助: http://www.depesz.com/index.php/2008/06/18/conditional-ddl/

I'm looking for a way to script postgreSQL schema changes in an idempotent manner.

In MSSQL I could do something like this:

if(not exists(select * from information_schema.columns where table_name = 'x' and column_name = 'y'))
begin
    alter table x add y int
end
go

PostgreSQL doesn't seem to allow ad-hoc pl/pgsql in the same way MSSQL does with T-SQL so I can't use control structures in a SQL script and run it with psql -f x.sql.

I know PostgreSQL will throw an error if the object already exists but I don't want to have to ignore errors.

I could use some schema versioning technique like dbdeploy but I really like the simplicity of running a set of files through psql without incurring any unwanted side effects.

Is this possible?

Thanks, Mark

解决方案

you might find this blogpost helpful: http://www.depesz.com/index.php/2008/06/18/conditional-ddl/

这篇关于幂等PostgreSQL DDL脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！