Mastering PostgreSQL 9.6

Modifying inherited structures

Once in a while data structures have to be modified. The ALTER TABLE clause is here to do exactly that. The question is: how can partitioned tables be modified?

Basically, all you have to do is tackle the parent table and add or remove columns. PostgreSQL will automatically propagate those changes through to the child tables and ensure that changes are made to all the relations as follows:

test=# ALTER TABLE t_data ADD COLUMN x int; ALTER TABLE test=# \d t_data_2016                        Table "public.t_data_2016"  Column  |  Type   |                      Modifiers                       ---------+---------+-----------------------------------------------------  id      | integer | not null default                      nextval('t_data_id_seq'::regclass)  t       | date    |   payload | text    |   x       | integer |