After doing full-time PostgreSQL consulting for over 16 years now, I actually don’t remember a time without inheritance anymore. Of course things were improved over time, but in my head it has always been there and it has always just worked as expected. After so many years I still love the feature because it offers some nice details, which are simply adorable. Here is one of those details I really like:
Let us assume that we create a parent table and two child tables:
test=# CREATE TABLE t_data (id serial, name text); CREATE TABLE test=# CREATE TABLE t_data_1 () INHERITS (t_data); CREATE TABLE test=# CREATE TABLE t_data_2 () INHERITS (t_data); CREATE TABLE
All tables look the same because both tables inherit columns from the parent table:
test=# \d t_data_2 Table "public.t_data_2" Column | Type | Modifiers --------+------------------------- id | integer | name | text | Inherits: t_data
Let us now add a column to one of the child tables:
test=# ALTER TABLE t_data_2 ADD COLUMN x int; ALTER TABLE
In PostgreSQL it is absolutely possible that a child table has more columns than the parent table. This is nothing special. However, what happens if we try to add the same column to the parent table as well?
test=# ALTER TABLE t_data ADD COLUMN x int; NOTICE: merging definition of column "x" for child "t_data_2" ALTER TABLE
PostgreSQL will cleverly merge those two columns because they are identical.
Dropping columns is also an easy task. Let us see what happens if “x” is dropped again:
test=# ALTER TABLE t_data DROP COLUMN x; ALTER TABLE test=# \d t_data Table "public.t_data" Column | Type | Modifiers --------+---------+----------------------- id | integer | name | text | Number of child tables: 2 (Use \d+ to list them.)
Of course dropping the table ensures that the column is also dropped in the child tables.
However, this does not hold true for t_data_2:
test=# \d t_data_2 Table "public.t_data_2" Column | Type | Modifiers --------+---------+----------------------- id | integer | name | text | x | integer | Inherits: t_data
Isn’t that wonderful? PostgreSQL “remembers” that this is a merged column and ensures that the column stays as it is. PostgreSQL only cascades the column removal to t_data_1, which also got the column through the parent table.