Inheritance is a concept taken from object-oriented databases. It opens up interesting new possibilities of database design.
Let's create two tables: cities and capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you are really clever you might invent some scheme like this:
CREATE TABLE capitals ( name text, population real, altitude int, -- (in ft) state char(2) ); CREATE TABLE non_capitals ( name text, population real, altitude int -- (in ft) ); CREATE VIEW cities AS SELECT name, population, altitude FROM capitals UNION SELECT name, population, altitude FROM non_capitals; |
A better solution is this:
CREATE TABLE cities ( name text, population real, altitude int -- (in ft) ); CREATE TABLE capitals ( state char(2) ) INHERITS (cities); |
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; |
name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 Madison | 845 (3 rows) |
On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude of 500 feet or higher:
SELECT name, altitude FROM ONLY cities WHERE altitude > 500; |
name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 (2 rows) |
Here the ONLY before cities indicates that the query should be run over only the cities table, and not tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed—SELECT, UPDATE and DELETE—support this ONLY notation.