Inheritance

Creating children tables

CREATE TABLE users (username text, email text);
CREATE TABLE simple_users () INHERITS (users);
CREATE TABLE users_with_password (password text) INHERITS (users);

Our three tables look like this:

users

|Column|Type |---|---|---|--- |username|text |email|text

simple_users

|Column|Type |---|---|---|--- |username|text |email|text

users_with_password

|Column|Type |---|---|---|--- |username|text |email|text |password|text

Altering tables

Let’s create two simple tables:

CREATE TABLE users (username text, email text);
CREATE TABLE simple_users () INHERITS (users);

Adding columns

ALTER TABLE simple_users ADD COLUMN password text;

simple_users

|Column|Type |---|---|---|--- |username|text |email|text |password|text

Adding the same column to the parent table will merge the definition of both columns:

ALTER TABLE users ADD COLUMN password text;

NOTICE: merging definition of column “password” for child “simple_users”

Dropping columns

Using our altered tables:

ALTER TABLE users DROP COLUMN password;

users

|Column|Type |---|---|---|--- |username|text |email|text

simple_users

|Column|Type |---|---|---|--- |username|text |email|text |password|text

Since we first added the column to simple_users, PostgreSQL makes sure this column isn’t dropped.

Now if we had another child table, its password column would, of course, have been dropped.

Remarks

An explanation as to why you would want to use inheritance in PostgreSQL is available here: http://stackoverflow.com/a/3075248/653378