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');