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 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 |
Check constraints:
"t_data_2016_t_check"
CHECK (t >= '2016-01-01'::date AND t < '2017-01-01'::date)
Inherits: t_data

As you can see, the column is added to the parent and automatically added to the child table here.

Note that this works for columns and so on. Indexes are a totally different story. In an inherited structure, every table has to be indexed separately. If you add an index to the parent table, it will only be present on the parent—it won't be deployed on those child tables. Indexing all those columns in all those tables is your task and PostgreSQL is not going to make those decisions for you. Of course, this can be seen as a feature or as a limitation. On the upside, you could say that PostgreSQL gives you all the flexibility to index things separately and therefore potentially more efficiently. However, people might also argue that deploying all those indexes one by one is a lot more work.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset