Mastering PostgreSQL 12 - Third Edition

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 here 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 TABLEtest=# \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 | ...