Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Idempotent PostgreSQL DDL scripts

Tags:

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