Is it possible to wrap DDL changes in a transaction in PostgreSQL?

sql postgresql transactions ddl
benstpierre picture benstpierre · Nov 19, 2012 · Viewed 10.8k times · Source

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.

Answer

Gary picture Gary · Nov 19, 2012

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 questions

Transactions are auto committed on PostgreSQL 9.5.2 with no option to change it?

I've just set up a new PostgreSQL 9.5.2, and it seems that all my transactions are auto committed. Running the following SQL: CREATE TABLE test (id NUMERIC PRIMARY KEY); INSERT INTO test (id) VALUES (1); ROLLBACK; results in a warning: WARNING: there …

Read More
What are the conditions for encountering a serialization failure?

The PostgreSQL manual page on the Serializable Isolation Level states: [Like] the Repeatable Read level, applications using this level must be prepared to retry transactions due to serialization failures. What are the conditions for encountering a serialization failure at the …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More