postgres 继承 Inheritance

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.For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500 feet:

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- in feet
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;

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:

SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;

 tableoid |   name    | altitude
----------+-----------+----------
   139793 | Las Vegas |     2174
   139793 | Mariposa  |     1953
   139798 | Madison   |      845

By doing a join with pg_class you can see the actual table names:

SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 AND c.tableoid = p.oid;

Another way to get the same effect is to use the regclass pseudo-type, which will print the table OID symbolically:

SELECT c.tableoid::regclass, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;

Inheritance does not automatically propagate data from INSERT or COPY commands to other tables in the inheritance hierarchy. In our example, the following INSERT statement will fail:

INSERT INTO cities (name, population, altitude, state)
VALUES ('Albany', NULL, NULL, 'NY');