3.5. PostgreSQL Inheritance

Inheritance is the concept of an object-oriented database. It opens the door to new and interesting possibilities for database design.

Let's create two tables: a cities table and a capitals table. Naturally, the capital is also the city (cities), so you want some way to show the capital implicitly when listing all the cities. If you are already very clear, then you may create a pattern like this:

CREATE TABLE Capitals (  name       text,  population real,  altitude   int,    --(units in feet)      State char (2)); CREATE TABLE non_capitals (  name       text,  population real,  altitude   int     --(units in feet)); CREATE VIEW cities as  SELECT name, population, altitude from capitals    UNION  SELECT name, population, Altit Ude from Non_capitals;

This method works fine if you're just querying, but it's hard to see if you need to update a few lines.

A better approach is to:

CREATE TABLE Cities (  name       text,  population real,  altitude   int     --(units in feet)); CREATE TABLE Capitals (  state      char (2)) INHERITS (cities);

In this example, the child table ( capitals ) inherits all the fields of its parent table ( cities ) (name, population, altitude ). The type text of the field name is the intrinsic type of PostgreSQL used for variable-length strings. The state capital has an additional field state that shows where it is located. In PostgreSQL, a table can inherit from 0 or more other tables.

For example, the following query finds the names of all cities above 500 feet above sea level, including the state capital:

SELECT name, altitude from  cities  WHERE altitude > 500;

It returns:

   Name    | altitude-----------+----------Las Vegas |     2174 Mariposa  |     1953 Madison   |      845 (3 rows)

On the other hand, the following query finds all cities that are not state capitals and are located at altitudes greater than or equal to 500 feet:

SELECT name, altitude from only    cities    WHERE altitude > 500;

   Name    | altitude-----------+----------Las Vegas |     2174 Mariposa  |     1953 (2 rows)

The only one in front of cities indicates that the system only cities runs queries against tables, not tables below the inheritance level cities . Many of the commands we have discussed SELECT, UPDATE, and DELETE support this only notation.

"Note" although inheritance is often useful, it does not yet integrate a unique constraint or foreign key, thus restricting its usefulness.

For more information refer to http://www.infocool.net/PostgreSQL/index.htm

3.5. PostgreSQL Inheritance