Postgresql 中文操作指南

5.10. Inheritance #

PostgreSQL 实现了表继承，它可以成为数据库设计人员的有用工具。（SQL:1999 以及更高版本定义了类型继承功能，它在很多方面与此处描述的功能不同。）

PostgreSQL implements table inheritance, which can be a useful tool for database designers. (SQL:1999 and later define a type inheritance feature, which differs in many respects from the features described here.)

让我们从一个示例开始：假设我们尝试构建城市的数据模型。每个州都有很多城市，但只有一个首府。我们希望能够快速检索任何特定州的首府。这可以通过创建两个表来实现，一个用于州首府，另一个用于不是首府的城市。但是，当我们想要询问有关城市的数据时，无论它是不是首府会怎样？继承功能可以帮助解决这个问题。我们定义 capitals 表，以便它从 cities 中继承：

Let’s start with an example: suppose we are trying to build a data model for cities. Each state has many cities, but only one capital. We want to be able to quickly retrieve the capital city for any particular state. This can be done by creating two tables, one for state capitals and one for cities that are not capitals. However, what happens when we want to ask for data about a city, regardless of whether it is a capital or not? The inheritance feature can help to resolve this problem. We define the capitals table so that it inherits from cities:

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

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

在这种情况下，capitals 表 inherits 其父表 cities 的所有列。州首府还有额外的列 state，它显示其州。

In this case, the capitals table inherits all the columns of its parent table, cities. State capitals also have an extra column, state, that shows their state.

在 PostgreSQL 中，一个表可以从零个或更多其他表中继承，而查询可以引用表的全部行，或者表的所有行及其所有后代表的全部行。后者行为是默认值。例如，以下查询找到位于 500 英尺以上的所有城市（包括州首府）的名称：

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. The latter behavior is the default. 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;

给定 PostgreSQL 教程中的示例数据（参见 Section 2.1），这会得到：

Given the sample data from the PostgreSQL tutorial (see Section 2.1), this returns:

   name    | elevation
-----------+-----------
 Las Vegas |      2174
 Mariposa  |      1953
 Madison   |       845

另一方面，以下查询将找到所有不是州首府且位于高过 500 英尺高度的城市：

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

此处的 ONLY 关键字表示查询应仅适用于 cities，而不适用于继承层次结构中 cities 下面的任何表。我们已经讨论过的很多命令——SELECT、UPDATE 和 DELETE——都支持 ONLY 关键字。

Here the ONLY keyword indicates that the query should apply only to cities, and not any tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed — SELECT, UPDATE and DELETE — support the ONLY keyword.

你还可以撰写表名称，后跟 *，以明确指定包括后代表：

You can also write the table name with a trailing * to explicitly specify that descendant tables are included:

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

撰写 * 没有必要，因为此行为总是默认值。但是，此语法仍然受支持，以与可以更改默认值的旧版本保持兼容。

Writing * is not necessary, since this behavior is always the default. However, this syntax is still supported for compatibility with older releases where the default could be changed.

在某些情况下，你可能想知道特定的行最初来自哪个表。每个表中都有一个称为 tableoid 的系统列，它可以告诉你原始表：

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.elevation
FROM cities c
WHERE c.elevation > 500;

返回：

which returns:

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

（如果你尝试复制此示例，则可能获得不同的数字 OID。）通过与 pg_class 进行连接，你可以看到实际的表名称：

(If you try to reproduce this example, you will probably get different numeric OIDs.) By doing a join with pg_class you can see the actual table names:

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

返回：

which returns:

 relname  |   name    | elevation
----------+-----------+-----------
 cities   | Las Vegas |      2174
 cities   | Mariposa  |      1953
 capitals | Madison   |       845

获得相同效果的另一种方法是使用 regclass 别名类型，它将以符号形式打印表 OID：

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

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

继承不会自动将数据从 INSERT 或 COPY 命令传播到继承层次结构中的其他表。在我们的示例中，以下 INSERT 语句将会失败：

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, elevation, state)
VALUES ('Albany', NULL, NULL, 'NY');

我们可能会希望能够以某种方式将数据路由到 capitals 表格中，但这不会发生：INSERT 总会将其精确插入指定的表格中。在某些情况下，可以使用规则重定向插入（参见 Chapter 41）。然而那对于上述情况没有帮助，因为 cities 表格不包含 state 列，因此该命令会在应用规则之前被拒绝。

We might hope that the data would somehow be routed to the capitals table, but this does not happen: INSERT always inserts into exactly the table specified. In some cases it is possible to redirect the insertion using a rule (see Chapter 41). However that does not help for the above case because the cities table does not contain the column state, and so the command will be rejected before the rule can be applied.

除非使用 NO INHERIT 子句明确指定，否则父表上的所有检查约束和非空约束都会自动被其子表继承。其他类型的约束（唯一、主键和外键约束）不会被继承。

All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.

一张表可以从多于一个父表中继承，在该情况下，它具有由父表定义的列的并集。在子表定义中声明的任何列都会添加到这些列中。如果同一列名出现在多个父表中，或出现在父表和子定义这两处，那么这些列会"合并"，以便子表中只出现一个这样的列。要合并，各列必须有相同的数据类型，否则会引发错误。可继承的检查约束和非空约束以类似的方式合并。因此，例如，如果一个合并的列的某个列定义标记为非空，那么该合并列将被标记为非空。如果检查约束有相同名称，那么它们会被合并，如果它们有不同的条件，那么合并将会失败。

A table can inherit from more than one parent table, in which case it has the union of the columns defined by the parent tables. Any columns declared in the child table’s definition are added to these. If the same column name appears in multiple parent tables, or in both a parent table and the child’s definition, then these columns are “merged” so that there is only one such column in the child table. To be merged, columns must have the same data types, else an error is raised. Inheritable check constraints and not-null constraints are merged in a similar fashion. Thus, for example, a merged column will be marked not-null if any one of the column definitions it came from is marked not-null. Check constraints are merged if they have the same name, and the merge will fail if their conditions are different.

通常在使用 CREATE TABLE 语句的 INHERITS 子句创建子表是建立表继承。或者，可以使用 ALTER TABLE 的 INHERIT 变体，添加现有已在兼容模式下定义的表的新的父关系。为此，新的子表必须包含与父表的列具有相同名称和类型的列。它还必须包含与父表的名称和检查表达式相同的检查约束。同样，可以使用 ALTER TABLE 的 NO INHERIT 变体，从子表中删除继承链接。像这样动态添加和删除继承链接在将继承关系用于表分区时很有用（参见 Section 5.11 ）。

Table inheritance is typically established when the child table is created, using the INHERITS clause of the CREATE TABLE statement. Alternatively, a table which is already defined in a compatible way can have a new parent relationship added, using the INHERIT variant of ALTER TABLE. To do this the new child table must already include columns with the same names and types as the columns of the parent. It must also include check constraints with the same names and check expressions as those of the parent. Similarly an inheritance link can be removed from a child using the NO INHERIT variant of ALTER TABLE. Dynamically adding and removing inheritance links like this can be useful when the inheritance relationship is being used for table partitioning (see Section 5.11).

创建兼容表的一种便捷方法是，稍后会将其设定为新子，可以参照 CREATE TABLE 中的 LIKE 子句。这会创建一个具有与源表相同的列的新表。如果在源表上定义有任何 CHECK 约束，那么应该指定 LIKE 的 INCLUDING CONSTRAINTS 选项，因为新子必须具有与父匹配的约束，才能被视为兼容。

One convenient way to create a compatible table that will later be made a new child is to use the LIKE clause in CREATE TABLE. This creates 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 to LIKE should be specified, as the new child must have constraints matching the parent to be considered compatible.

当子表仍然存在时无法删除父表。如果子表的列或检查约束是从父表继承的，那么也无法删除或更改。如果你希望删除一个表格及其所有子孙，一种简单的方法是使用 CASCADE 选项删除父表（参见 Section 5.14）。

A parent table cannot be dropped while any of its children remain. Neither can columns or check constraints of child tables be dropped or altered if they are inherited from any parent tables. If you wish to remove a table and all of its descendants, one easy way is to drop the parent table with the CASCADE option (see Section 5.14).

ALTER TABLE 将传播继承层次中的列数据定义和检查约束中的任何更改。同样，只有在使用 CASCADE 选项时，才能删除其他表依赖的列。ALTER TABLE 遵循与在 CREATE TABLE 期间应用的重复列合并和拒绝相同的规则。

ALTER TABLE will propagate any changes in column data definitions and check constraints down the inheritance hierarchy. Again, dropping columns that are depended on by other tables is only possible when using the CASCADE option. ALTER TABLE follows the same rules for duplicate column merging and rejection that apply during CREATE TABLE.

继承的查询仅在父表格上执行访问权限检查。因此，例如，在 cities 表格上授予 UPDATE 权限表示也有权通过 cities 访问 capitals 表格中的行。这保持了如同数据（也）在父表格中的外观。但是如果没有额外的授予，就不能直接更新 capitals 表格。以类似方式，在继承的查询期间，父表格的行安全策略（参见 Section 5.8）将应用于来自子表格的行。如果子表格有任何策略，则仅在明确在其查询中对表格命名时应用，并且在那种情况下，会忽略附加到其父元素的任何策略。

Inherited queries perform access permission checks on the parent table only. Thus, for example, granting UPDATE permission on the cities table implies permission to update rows in the capitals table as well, when they are accessed through cities. This preserves the appearance that the data is (also) in the parent table. But the capitals table could not be updated directly without an additional grant. In a similar way, the parent table’s row security policies (see Section 5.8) are applied to rows coming from child tables during an inherited query. A child table’s policies, if any, are applied only when it is the table explicitly named in the query; and in that case, any policies attached to its parent(s) are ignored.

外部表格（参见 Section 5.12）也可以是继承层次的一部分，作为父表格或子表格，就像普通表格一样。如果外部表格是继承层次的一部分，那么对整个层次来不支持外部表格的任何操作。

Foreign tables (see Section 5.12) can also be part of inheritance hierarchies, either as parent or child tables, just as regular tables can be. If a foreign table is part of an inheritance hierarchy then any operations not supported by the foreign table are not supported on the whole hierarchy either.

5.10.1. Caveats #

请注意，并非所有 SQL 命令都能够处理继承层次。用于数据查询、数据修改或模式修改的命令（例如，SELECT、UPDATE、DELETE、ALTER TABLE 的大多数变体，但不包括 INSERT 或 ALTER TABLE …​ RENAME）通常默认包含子表，并支持 ONLY 符号来排除它们。用于数据库维护和调优的命令（例如，REINDEX、VACUUM）通常仅作用于单个物理表格，并且不支持对继承层次进行递归。每个单独命令的相应行为都在其参考页面（ SQL Commands）中进行了说明。

Note that not all SQL commands are able to work on inheritance hierarchies. Commands that are used for data querying, data modification, or schema modification (e.g., SELECT, UPDATE, DELETE, most variants of ALTER TABLE, but not INSERT or ALTER TABLE …​ RENAME) typically default to including child tables and support the ONLY notation to exclude them. Commands that do database maintenance and tuning (e.g., REINDEX, VACUUM) typically only work on individual, physical tables and do not support recursing over inheritance hierarchies. The respective behavior of each individual command is documented in its reference page (SQL Commands).

继承功能的一个严重限制是，索引（包括唯一约束）和外键约束只适用于单表，不适用于它们的继承子。这对一个外键约束的引用和被引用两侧而言都是真实的。因此，根据上述示例：

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms of the above example:

尚未对继承层次实现的一些功能已对声明式分区实现。在决定继承的具有传统意义的分区是否对你的应用程序有用时，需要格外谨慎。

Some functionality not implemented for inheritance hierarchies is implemented for declarative partitioning. Considerable care is needed in deciding whether partitioning with legacy inheritance is useful for your application.