PostgreSQL implements table
inheritance, which can be a useful tool for database designers.
(SQL:1999 and later define a type inheritance feature, which
differs in many respects from the features described here.)

Let's start with an example: suppose we are trying to build a
data model for cities. Each state has many cities, but only one
capital. We want to be able to quickly retrieve the capital city
for any particular state. This can be done by creating two
tables, one for state capitals and one for cities that are not
capitals. However, what happens when we want to ask for data
about a city, regardless of whether it is a capital or not? The
inheritance feature can help to resolve this problem. We define
the capitals table so that it
inherits from cities:

In this case, the capitals table
inherits all the columns of its parent
table, cities. State capitals also
have an extra column, state, that
shows their state.

In PostgreSQL, a table can
inherit from zero or more other tables, and a query can reference
either all rows of a table or all rows of a table plus all of its
descendant tables. The latter behavior is the default. For
example, the following query finds the names of all cities,
including state capitals, that are located at an altitude over
500 feet:

SELECT name, altitude
FROM cities
WHERE altitude > 500;

Given the sample data from the PostgreSQL tutorial (see Section 2.1), this returns:

Here the ONLY keyword indicates that
the query should apply only to cities, and not any tables below cities in the inheritance hierarchy. Many of
the commands that we have already discussed — SELECT, UPDATE and
DELETE — support the ONLY keyword.

You can also write the table name with a trailing * to explicitly specify that descendant tables are
included:

SELECT name, altitude
FROM cities*
WHERE altitude > 500;

Writing * is not necessary, since
this behavior is the default (unless you have changed the setting
of the sql_inheritance
configuration option). However writing *
might be useful to emphasize that additional tables will be
searched.

In some cases you might wish to know which table a particular
row originated from. There is a system column called tableoid in each table which can tell you the
originating table:

We might hope that the data would somehow be routed to the
capitals table, but this does not
happen: INSERT always inserts into
exactly the table specified. In some cases it is possible to
redirect the insertion using a rule (see Chapter 36). However that does not help for the
above case because the cities table
does not contain the column state,
and so the command will be rejected before the rule can be
applied.

All check constraints and not-null constraints on a parent
table are automatically inherited by its children. Other types of
constraints (unique, primary key, and foreign key constraints)
are not inherited.

A table can inherit from more than one parent table, in which
case it has the union of the columns defined by the parent
tables. Any columns declared in the child table's definition are
added to these. If the same column name appears in multiple
parent tables, or in both a parent table and the child's
definition, then these columns are "merged" so that there is only one such column in
the child table. To be merged, columns must have the same data
types, else an error is raised. The merged column will have
copies of all the check constraints coming from any one of the
column definitions it came from, and will be marked not-null if
any of them are.

Table inheritance is typically established when the child
table is created, using the INHERITS
clause of the CREATE
TABLE statement. Alternatively, a table which is already
defined in a compatible way can have a new parent relationship
added, using the INHERIT variant of
ALTER TABLE. To do
this the new child table must already include columns with the
same names and types as the columns of the parent. It must also
include check constraints with the same names and check
expressions as those of the parent. Similarly an inheritance link
can be removed from a child using the NO
INHERIT variant of ALTER TABLE.
Dynamically adding and removing inheritance links like this can
be useful when the inheritance relationship is being used for
table partitioning (see Section
5.9).

One convenient way to create a compatible table that will
later be made a new child is to use the LIKE clause in CREATE
TABLE. This creates a new table with the same columns as the
source table. If there are any CHECK
constraints defined on the source table, the INCLUDING CONSTRAINTS option to LIKE should be specified, as the new child must
have constraints matching the parent to be considered
compatible.

A parent table cannot be dropped while any of its children
remain. Neither can columns or check constraints of child tables
be dropped or altered if they are inherited from any parent
tables. If you wish to remove a table and all of its descendants,
one easy way is to drop the parent table with the CASCADE option.

ALTER TABLE will
propagate any changes in column data definitions and check
constraints down the inheritance hierarchy. Again, dropping
columns that are depended on by other tables is only possible
when using the CASCADE option.
ALTER TABLE follows the same rules for
duplicate column merging and rejection that apply during
CREATE TABLE.

Table access permissions are not automatically inherited.
Therefore, a user attempting to access a parent table must
either have permissions to do the same operation on all its
child tables as well, or must use the ONLY notation. When adding a new child table to
an existing inheritance hierarchy, be careful to grant all the
needed permissions on it.

More generally, note that not all SQL commands are able to
work on inheritance hierarchies. Commands that are used for
data querying, data modification, or schema modification (e.g.,
SELECT, UPDATE, DELETE, most
variants of ALTER TABLE, but not
INSERT or ALTER
TABLE ... RENAME) typically default to including child
tables and support the ONLY notation
to exclude them. Commands that do database maintenance and
tuning (e.g., REINDEX, VACUUM) typically only work on individual,
physical tables and do not support recursing over inheritance
hierarchies. The respective behavior of each individual command
is documented in its reference page (Reference I, SQL Commands).

A serious limitation of the inheritance feature is that
indexes (including unique constraints) and foreign key
constraints only apply to single tables, not to their
inheritance children. This is true on both the referencing and
referenced sides of a foreign key constraint. Thus, in the
terms of the above example:

If we declared cities.name
to be UNIQUE or a PRIMARY KEY, this would not stop the
capitals table from having rows
with names duplicating rows in cities. And those duplicate rows would by
default show up in queries from cities. In fact, by default capitals would have no unique constraint
at all, and so could contain multiple rows with the same
name. You could add a unique constraint to capitals, but this would not prevent
duplication compared to cities.

Similarly, if we were to specify that cities.nameREFERENCES some other table, this
constraint would not automatically propagate to capitals. In this case you could work
around it by manually adding the same REFERENCES constraint to capitals.

Specifying that another table's column REFERENCES cities(name) would allow the
other table to contain city names, but not capital names.
There is no good workaround for this case.

These deficiencies will probably be fixed in some future
release, but in the meantime considerable care is needed in
deciding whether inheritance is useful for your
application.