Movatterモバイル変換

Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

Let's create two tables: A tablecities and a tablecapitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you're really clever you might invent some scheme like this:

CREATE TABLE capitals (  name       text,  population real,  elevation  int,    -- (in ft)  state      char(2));CREATE TABLE non_capitals (  name       text,  population real,  elevation  int     -- (in ft));CREATE VIEW cities AS  SELECT name, population, elevation FROM capitals    UNION  SELECT name, population, elevation FROM non_capitals;

This works OK as far as querying goes, but it gets ugly when you need to update several rows, for one thing.

A better solution is this:

CREATE TABLE cities (  name       text,  population real,  elevation  int     -- (in ft));CREATE TABLE capitals (  state      char(2) UNIQUE NOT NULL) INHERITS (cities);

In this case, a row ofcapitalsinherits all columns (name,population, andelevation) from itsparent,cities. The type of the columnname istext, a nativePostgreSQL type for variable length character strings. Thecapitals table has an additional column,state, which shows its state abbreviation. InPostgreSQL, a table can inherit from zero or more other tables.

For example, the following query finds the names of all cities, including state capitals, that are located at an elevation over 500 feet:

SELECT name, elevation  FROM cities  WHERE elevation > 500;

which returns:

   name    | elevation-----------+----------- 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 elevation over 500 feet:

SELECT name, elevation    FROM ONLY cities    WHERE elevation > 500;

   name    | elevation-----------+----------- Las Vegas |      2174 Mariposa  |      1953(2 rows)

Here theONLY beforecities indicates that the query should be run over only thecities table, and not tables belowcities in the inheritance hierarchy. Many of the commands that we have already discussed —SELECT,UPDATE, andDELETE — support thisONLY notation.