Sql – Is it possible to wrap DDL changes in a transaction in PostgreSQL

ddlpostgresqlsqltransactions

I know that in MySQL ddl statements such as alter table/create table/etc cause an implicit transaction commit.

As we are moving to PostgreSQL is it possible to wrap multiple DDL statments in a transaction?

This would make migration scripts a lot more robust, a failed DDL change would cause everything to rollback.

Best Answer

DDL statements are covered by transactions. I can't find the relevant section in the official documentation, but have provided a link to the wiki which covers it.

Just remember that transactions aren't automatically opened in postgresql, you must start them with BEGIN or START TRANSACTION.

Postgresql Wiki about Transactional DDL

Related Solutions

R – Wrap an Oracle schema update in a transaction

You can not turn this off. Fairly easy to work around by designing your scripts to drop tables in the event they already exist etc...

You can look at using FLASHBACK database, I believe you can do this at the schema/object level but check the docs to confirm that. You would need to be on 10G for that to work.

PostgreSQL “DESCRIBE TABLE”

Try this (in the psql command-line tool):

\d+ tablename

See the manual for more info.

Best Answer

Related Solutions

R – Wrap an Oracle schema update in a transaction

PostgreSQL “DESCRIBE TABLE”

Related Topic