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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With