# 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 |
| text |
# simple_users
| Column | Type |
|---|---|
| username | text |
| text |
# users_with_password
| Column | Type |
|---|---|
| username | text |
| 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 |
| 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 |
| text |
# simple_users
| Column | Type |
|---|---|
| username | text |
| 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 (opens new window)