Mastering PostgreSQL 12 - Third Edition

Transactional DDLs

PostgreSQL has a very nice feature that is unfortunately not present in many commercial database systems. In PostgreSQL, it is possible to run DDLs (commands that change the data's structure) inside a transaction block. In a typical commercial system, a DDL will implicitly commit the current transaction. This does not occur in PostgreSQL.

Apart from some minor exceptions (DROP DATABASE, CREATE TABLESPACE, DROP TABLESPACE, and so on), all DDLs in PostgreSQL are transactional, which is a huge advantage and a real benefit to end users.

Here is an example:

test=# \dNo relations found. test=# BEGIN; 
BEGIN test=# CREATE TABLE t_test (id int); 
CREATE TABLE test=# ALTER TABLE t_test ALTER COLUMN id TYPE int8; 
ALTER TABLE test=# ...