PostgreSQL Inheritance Detailed

PostgreSQL implements table inheritance, a feature that is an effective tool for database designers. The SQL99 and later standards define the type inheritance characteristics, which are different from the many features we describe here.

Let's start with an example: Let's say we're trying to make a city data model. There are many cities in each state, but there is only one capital city. We want to be able to quickly retrieve the capitals of any state. This task can be achieved by creating two tables, one for the state government, and one for the non-State Government table. But what if we don't want to find out what the city is going to do? Inherited features can help us solve this problem. We define theCapitalstable, which inherits from theCitiestable:

CREATE TABLE cities (
    name text,
    population float,
    altitude int-feet
);
CREATE TABLE capitals (
    state char (2)
) INHERITS (cities);
In this case, the capitals table inherits all the attributes in its parent table cities. State capitals have an additional state attribute that shows their state.

In PostgreSQL, a table can inherit attributes from zero or more other tables, and a query can refer to all rows in a table, or it can refer to a table and all the rows it represents (the latter is the default behavior). For example, the following query finds all city names above 500 feet, including state capitals:

SELECT name, altitude
    FROM cities
    WHERE altitude> 500;
Using the data from the PostgreSQL tutorial, it returns:

   name | altitude
----------- + ----------
 Las Vegas | 2174
 Mariposa | 1953
 Madison | 845
On the other hand, if you want to find all cities that do not include the capital of the state with an altitude of more than 500 feet, the query should look like this:

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

   name | altitude
----------- + ----------
 Las Vegas | 2174
 Mariposa | 1953
ONLY in front of cities indicates that the query should only target cities and not its descendants. Many of the commands we have discussed (SELECT, UPDATE and DELETE) support the ONLY keyword.

You can also write a * after the table name to indicate that all post representatives are included:

SELECT name, altitude
    FROM cities *
    WHERE altitude> 500;
Because this behavior is the default, writing * is not necessary (unless you have changed the configuration options in sql_inheritance). However, writing * can be used to emphasize searching additional tables.

Sometimes you may want to know which table a row version comes from. In each table we have a tableoid system property that can tell you who the source table is:

SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude> 500;
The results are as follows (you may get different OIDs):

 tableoid | name | altitude
---------- + ----------- + ----------
   139793 | Las Vegas | 2174
   139793 | Mariposa | 1953
   139798 | Madison | 845
By making a connection with pg_class, you can see the actual table name:

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

 relname | name | altitude
---------- + ----------- + ----------
 cities | Las Vegas | 2174
 cities | Mariposa | 1953
 capitals | Madison | 845
For INSERT or COPY, inheritance does not automatically affect subsequent representatives. In our example, the following INSERT statement will fail:

INSERT INTO cities (name, population, altitude, state)
VALUES (‘New York’, NULL, NULL, ‘NY’);
We may want the data to be transferred to the capitals table, but this does not happen: INSERT always inserts the table that is explicitly declared. In some cases, we can use rules for redirect insertion. However, it cannot help the above example, because the cities table does not contain the state field, so the command will be rejected before the rule is applied.

All check constraints and non-null constraints of the parent table will be automatically inherited by all child tables. However, other types of constraints (unique, primary key, foreign key constraints) will not be inherited.

A child table can inherit from multiple parent tables, in this case it will have the sum of all parent table fields, and the fields defined in the child table will also be added to it. If the same field name appears in multiple parent tables, or both in the definition of the parent table and the child table, then these fields will be "fused" so that there is only one such field in the child table. To merge, the data types of the fields must be the same, otherwise an error will be thrown. The fused field will have all the check constraints of its parent field, and if a parent field has a non-null constraint, the fused field must also be non-null.

Table inheritance is usually defined using the CREATE TABLE statement with the INHERITS clause. In addition, a child table that has been defined in this way can use the ALTER TABLE command with INHERIT to add a new parent table. Note: The child table must already contain all the fields of the new parent table and have the same type. In addition, the name and expression of each constraint of the new parent table must be included in this child table. Similarly, an inheritance chain can be deleted from the child table using the ALTER TABLE command with NO INHERIT. Allowing dynamic addition and deletion of inheritance chains is useful for table partitioning based on inheritance relationships.

A convenient way to create a new table that will be a subtable is to use the CREATE TABLE command with a LIKE clause. It will create a new table with the same fields as the source table. If there are constraints in the source table, you should specify the INCLUDING CONSTRAINTS option of LIKE, because the child table must contain the CHECK constraints in the source table.

Any parent table that exists in a child table cannot be deleted. Similarly, any fields or constraints inherited from the parent table in the child table cannot be deleted or modified. If you want to delete a table and all its descendants, the easiest way is to use the CASCADE option to delete the parent table.

ALTER TABLE will propagate all data definitions and check constraints to future generations. In addition, you can only delete fields that depend on other tables if you use the CASCADE option. ALTER TABLE has the same rules as CREATE TABLE in terms of repeated field fusion and rejection.

Note how the table access permissions are handled. Accessing the parent table will automatically access the data in the child table without the need for more access permission checks. This preserves the performance of the data in the parent table. However, direct access to the child table does not automatically allow access to the parent table, and further permissions need to be granted to access the parent table.

caveat
Note that not all SQL commands can work properly at all inheritance levels. Data query, data modification, and schema modification commands (such as SELECT, UPDATE, DELETE, most variants of ALTER TABLE, but not INSERT and ALTER TABLE ... RENAME) typically include subtables and support ONLY symbols to exclude them. Commands for database maintenance and tuning (such as REINDEX, VACUUM) usually only work for individual, physical table does not support recursion beyond the inheritance hierarchy. The individual actions of the individual commands are recorded in their reference pages.

A serious limitation of inheritance is that indexes (including unique constraints) and foreign key constraints can only be used for a single table, and cannot include their sub-tables (regardless of whether the reference table or the referenced table of the foreign key constraint), therefore, In the above example:

Even if we declare cities.name as UNIQUE or PRIMARY KEY, it will not prevent the capitals table from having duplicate names in the cities data row. And these duplicate rows will be displayed when querying the cities table. In fact, by default, capitals will have no unique constraints at all, and therefore may contain multiple rows with the same name. You should add unique constraints to capitals, but even doing so will not avoid duplication with cities.

Similarly, even if we declare that cities.name refers to (REFERENCES) some other table, this constraint will not be automatically propagated to the capitals table. Under this condition, you can do this by manually adding the same REFERENCES constraint to the capitals table.

Declaring a field in another table as REFERENCES cities (name) will allow the other table to contain the city name, but not the capital name. There is no good workaround in this situation.

These shortcomings are likely to be patched in future versions, but you also need to consider whether inheritance is really useful for your application.

Detailed explanation of PostgreSQL inheritance