Mastering PostgreSQL 10

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 structure) inside a transaction block. In a typical commercial system, a DDL will implicitly commit the current transaction. Not so 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 plus 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=# \d t_test Table "public.t_test" ...