PostgreSQL implements table inheritance, which is a useful tool for database designers (SQL:1999 and later versions define a type inheritance feature, but it is very different from the inheritance introduced here). Let's start with an example: suppose we want to build a data model for a city. Each state has many cities, but only one capital.
Simple Application Server
USD1.00 New User Coupon
* Only 3,000 coupons available.
* Each new user can only get one coupon(except users from distributors).
* The coupon is valid for 30 days from the date of receipt.
We want to be able to quickly retrieve the capital city of any particular state. This can be achieved by creating two tables: one for state capitals and the other for cities that are not capitals. However, what happens when we want to view the data of a city (whether it is a capital or not)? Inheritance features will help solve this problem. We can define the capitals table as inherited from the cities table:
jydb=# CREATE TABLE cities (
jydb(# name text,
jydb(# population float,
jydb(# altitude int - in feet
jydb(# );
CREATE TABLE
jydb=# CREATE TABLE capitals (
jydb(# state char(2)
jydb(#) INHERITS (cities);
CREATE TABLE
jydb=# insert into cities values('Las Vegas',600,2174);
INSERT 0 1
jydb=# insert into cities values('Mariposa',500,1953);
INSERT 0 1
jydb=# insert into cities values('Madison',450,845);
INSERT 0 1
jydb=# insert into capitals values('Houston',400,745,'LA');
INSERT 0 1
jydb=# select * from cities;
name | population | altitude
-----------+------------+----------
Las Vegas | 600 | 2174
Mariposa | 500 | 1953
Madison | 450 | 845
Houston | 400 | 745
(4 rows)
jydb=# select * from capitals;
name | population | altitude | state
---------+------------+----------+-------
Houston | 400 | 745 | LA
(1 row)
In this case, the capitals table inherits all the columns of its parent table cities. The state capital has an additional column state to indicate the state to which it belongs.
In PostgreSQL, a table can inherit from 0 or more other tables, and a query on a table can refer to all rows of a table or all rows of the table plus all its back representatives. The default is the latter behavior. For example, the following query will find the names of all cities above 500 feet above sea level, including state capitals:
jydb=# SELECT name, altitude FROM cities WHERE altitude> 500;
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
Houston | 745
(4 rows)
On the other hand, the following query will find all cities whose altitude is more than 500 feet and are not state capitals:
jydb=# SELECT name, altitude FROM ONLY cities WHERE altitude> 500;
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)
The ONLY keyword here indicates that the query is only applied to cities, and other tables below cities in the inheritance hierarchy will not be involved in the query. Many of the commands we have discussed (such as SELECT, UPDATE, and DELETE) support the ONLY keyword.
We can also write a * after the table name to explicitly include the back representative in the query scope:
jydb=# SELECT name, altitude FROM cities* WHERE altitude> 500;
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
Houston | 745
(4 rows)
*It is not necessary, because its corresponding behavior is the default (unless the sql_inheritance configuration option is changed). But writing * helps to emphasize that additional tables will be searched.
In some cases, we may want to know which table a particular row comes from. The system column tableoid in each table can tell us which table the row comes from:
jydb=# SELECT c.tableoid, c.name, c.altitude FROM cities c WHERE c.altitude> 500;
tableoid | name | altitude
----------+-----------+----------
24653 | Las Vegas | 2174
24653 | Mariposa | 1953
24653 | Madison | 845
24659 | Houston | 745
(4 rows)
(If you regenerate this result, you may get a different OID number.) You can see the actual table name by connecting with pg_class:
jydb=# SELECT p.relname, c.name, c.altitude
jydb-# FROM cities c, pg_class p
jydb-# WHERE c.altitude> 500 AND c.tableoid = p.oid;
relname | name | altitude
----------+-----------+----------
cities | Las Vegas | 2174
cities | Mariposa | 1953
cities | Madison | 845
capitals | Houston | 745
(4 rows)
Another way to get the same effect is to use the regclass pseudotype, which will symbolically print out the OID of the table:
jydb=# SELECT c.tableoid::regclass, c.name, c.altitude
jydb-# FROM cities c
jydb-# WHERE c.altitude> 500;
tableoid | name | altitude
----------+-----------+----------
cities | Las Vegas | 2174
cities | Mariposa | 1953
cities | Madison | 845
capitals | Houston | 745
(4 rows)
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:
jydb=# INSERT INTO cities (name, population, altitude, state) VALUES (’Albany’, NULL, NULL, ’NY’);
ERROR: column "state" of relation "cities" does not exist
LINE 1: INSERT INTO cities (name, population, altitude, state) VALUE...
^
We may want data to be introduced into the capitals table in some way, but this will not happen: INSERT always inserts into the specified table. In some cases, the insertion action can be redirected by using a rule (see Chapter 39). But this does not help in the above situation, because the cities table does not contain the state column at all, so this command will be rejected before the rule is triggered.
All check constraints and non-null constraints on the parent table will automatically be inherited by its descendants. Other types of constraints (unique, primary key, and foreign key constraints) will not be inherited.
A table can inherit from multiple parent tables, in which case it has the union of the columns defined by the parent tables. Any columns defined on the child table will also be added to it. If there are duplicate columns in this set, then these columns will be "merged" so that there will only be one such column in the child table. The premise that columns with duplicate names can be merged is that these columns must have the same data type, otherwise an error will result. Inheritable check constraints and non-null constraints are combined in a similar way. So, for example, if any column definition is marked as not-null, the combined column will be marked as not-null. If the check constraints have the same name, they will be merged; if the conditions are different, the merge will fail.
Table inheritance is usually established when the child table is created, using the INHERITS clause of the CREATE TABLE statement. An already created table can also add a new parent relationship in another way, using the INHERIT variant of ALTER TABLE. To do this, the new child table must already include columns with the same name and data type as the parent table. The child table must also include the same check constraints and check expressions as the parent table. Similarly, an inheritance link can also be removed from a child table using the NO INHERIT variant of ALTER TABLE. Dynamically add and remove inheritance links can be used to achieve table partitioning
One way to create a new table that will be used as a child in the future is to use the LIKE clause in CREATE TABLE. This will create a new table with the same columns as the source table. If there are any CHECK constraints defined on the source table, the INCLUDING CONSTRAINTS option of LIKE can be used to make the new child table also contain the same constraints as the parent table.
When any child table exists, the parent table cannot be deleted. When the columns or check constraints of the child table are inherited from the parent table, they cannot be deleted or modified. If you want to remove a table and all its descendants, an easy way is to use the CASCADE option to delete the parent table
ALTER TABLE will propagate any changes in column data definitions or check constraints down the inheritance hierarchy. Similarly, to delete columns that are dependent on other tables can only use the CASCADE option. ALTER TABLE follows the same rules as CREATE TABLE for merging and rejecting duplicate columns.
Inheritance queries only perform access permission checks on the parent table. Therefore, for example, granting the update permission to the cities table means that the capitals table can be updated when the capitals table is accessed through cities. This reflects that the data in the child table is also in the parent table. But the capitals table cannot be updated directly without additional authorization. In a similar way, the row security policy of the parent table is applied to the row records of the child table when performing inheritance queries. The strategy of the child table (if any) is only applied if it is an explicitly named table in the query; in this case, any strategy attached to its parent will be ignored.
External tables can also be part of the inheritance hierarchy, that is, they can be used as parent or child tables, just like regular tables. If an external table is part of the inheritance hierarchy, then any operation not supported by the external table is not supported by the entire hierarchy.
