PG中的表继承和only关键词
作者：小麦苗 官网：http://postgres.cn/docs/13/ddl-inherit.html 相似于程序语言的类继承，PostgreSQL的表也可以继承（Table Inheritance）。创建继承表的操作如下： CREATE TABLE cities ( name text, population real, altitude int -- (in ft) ); -- 创建继承的表 CREATE TABLE capitals ( state char(2) ) INHERITS (cities); postgres=# \d+ cities Table 'public.cities' Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ------------+---------+-----------+----------+---------+----------+--------------+------------- name | text | | | | extended | | population | real | | | | plain | | altitude | integer | | | | plain | | Child tables: capitals Access method: heap postgres=# \d+ capitals Table 'public.capitals' Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ------------+--------------+-----------+----------+---------+----------+--------------+------------- name | text | | | | extended | | population | real | | | | plain | | altitude | integer | | | | plain | | state | character(2) | | | | extended | | Inherits: cities Access method: heap 这是先创建了cities表，然后创建了继承cities的capitals表。capitals的表中除了其特有的state列外，还含有和cities全部列相同的列。 因为表中没有内容所以看不出端倪，先插入数据。 insert into cities values ('南通',500,4); insert into capitals values('南京',700,9,'js'); postgres=# select * from cities; name | population | altitude ------+------------+---------- 南通 | 500 | 4 南京 | 700 | 9 (2 rows) postgres=# select * from capitals; name | population | altitude | state ------+------------+----------+------- 南京 | 700 | 9 | js (1 row) postgres=# select * from only cities; name | population | altitude ------+------------+---------- 南通 | 500 | 4 (1 row) postgres=# select * from cities*; name | population | altitude ------+------------+---------- 南通 | 500 | 4 南京 | 700 | 9 (2 rows) 第一个查询，是广义的，capitals继承了cities，所以capitals中的内容也会出现。 第二个查询，是查询capitals的。相当于单查黄狗。 第三个查询，是只查询cities中的，cities之前的only用于指示查询只在cities表上进行而不会涉及到继承层次中位于cities之下的其他表。select ，update，update都支持only关键字。 第四个查询，和第1个查询一样。在表后面写一个*表示显式指定包括所有子表。写*并不是必须的，不过写在表后面 可以用于强调搜索额外的子表。 假使我们想知道某个行版本来自哪个表？在每个表里我们都有一个tableoid系统属性可以告诉你源表是谁： postgres=# select tableoid,* from cities; tableoid | name | population | altitude ----------+------+------------+---------- 16384 | 南通 | 500 | 4 16390 | 南京 | 700 | 9 (2 rows) postgres=# select oid,relname from pg_class where oid in (16384,16390); oid | relname -------+---------- 16384 | cities 16390 | capitals (2 rows) postgres=# select p.relname,c.tableoid,c.name,c.altitude postgres-# from cities c,pg_class p postgres-# where c.tableoid=p.oid; relname | tableoid | name | altitude ----------+----------+------+---------- cities | 16384 | 南通 | 4 capitals | 16390 | 南京 | 9 (2 rows) 可以看到结果来源于两个表。 约束继承 使用INHERITS创建的新表会继承一个或多个父表，子表只会继承父表的表结构和NOT NULL，DEFAULT，CHECK三种约束，主键，外键和唯一键以及索引不会被继承，所以修改父表的结构（增删字段），NOT NULL，DEFAULT和CHECK约束会自动同步子表修改。 示例1. create table tbl_inherits_parent( a int not null, b varchar(32) not null default 'Got u', c int check (c > 0), d date not null ); alter table tbl_inherits_parent add constraint pk_tbl_inherits_parent_a primary key(a); alter table tbl_inherits_parent add constraint uk_tbl_inherits_parent_b_d unique (b,d); lhrdb1=# \d+ tbl_inherits_parent Table 'public.tbl_inherits_parent' Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------+-----------+----------+----------------------------+----------+--------------+------------- a | integer | | not null | | plain | | b | character varying(32) | | not null | 'Got u'::character varying | extended | | c | integer | | | | plain | | d | date | | not null | | plain | | Indexes: 'pk_tbl_inherits_parent_a' PRIMARY KEY, btree (a) 'uk_tbl_inherits_parent_b_d' UNIQUE CONSTRAINT, btree (b, d) Check constraints: 'tbl_inherits_parent_c_check' CHECK (c > 0) Access method: heap create table tbl_inherits_partition() inherits (tbl_inherits_parent); \d tbl_inherits_partition Table 'public.tbl_inherits_partition' Column | Type | Modifiers --------+-----------------------+--------------------------------------------- a | integer | not null b | character varying(32) | not null default 'Got u'::character varying c | integer | d | date | not null Check constraints: 'tbl_inherits_parent_c_check' CHECK (c > 0) Inherits: tbl_inherits_parent 示例2. alter table tbl_inherits_parent add column e int not null default 0; alter table tbl_inherits_parent alter column b set default 'try me'; \d tbl_inherits_partition Table 'public.tbl_inherits_partition' Column | Type | Modifiers --------+-----------------------+---------------------------------------------- a | integer | not null b | character varying(32) | not null default 'try me'::character varying c | integer | d | date | not null e | integer | not null default 0 Check constraints: 'tbl_inherits_parent_c_check' CHECK (c > 0) Inherits: tbl_inherits_parent 示例3. 除继承父表之外，创建子表时可以增加自己的字段 create table tbl_inherits_partition1(f int) inherits (tbl_inherits_parent); \d tbl_inherits_partition1 Table 'public.tbl_inherits_partition1' Column | Type | Modifiers --------+-----------------------+---------------------------------------------- a | integer | not null b | character varying(32) | not null default 'try me'::character varying c | integer | d | date | not null e | integer | not null default 0 f | integer | Check constraints: 'tbl_inherits_parent_c_check' CHECK (c > 0) Inherits: tbl_inherits_parent 示例4.解除继承 alter table tbl_inherits_partition1 no inherit tbl_inherits_parent; \d tbl_inherits_partition1 Table 'public.tbl_inherits_partition1' Column | Type | Modifiers --------+-----------------------+---------------------------------------------- a | integer | not null b | character varying(32) | not null default 'try me'::character varying c | integer | d | date | not null e | integer | not null default 0 f | integer | Check constraints: 'tbl_inherits_parent_c_check' CHECK (c > 0) 注意 父表上的所有检查约束和非空约束都将自动被它的后代所继承，除非显式地指定了NO INHERIT子句。其他类型的约束（唯一、主键和外键约束）则不会被继承。 一个表可以从超过一个的父表继承，在这种情况下它拥有父表们所定义的列的并集。任何定义在子表上的列也会被加入到其中。如果在这个集合中出现重名列，那么这些列将被“合并”，这样在子表中只会有一个这样的列。重名列能被合并的前提是这些列必须具有相同的数据类型，否则会导致错误。可继承的检查约束和非空约束会以类似的方式被合并。例如，如果合并成一个合并列的任一列定义被标记为非空，则该合并列会被标记为非空。如果检查约束的名称相同，则他们会被合并，但如果它们的条件不同则合并会失败。 表继承通常是在子表被创建时建立，使用CREATE TABLE语句的INHERITS子句。一个已经被创建的表也可以另外一种方式增加一个新的父亲关系，使用ALTER TABLE的INHERIT变体。要这样做，新的子表必须已经包括和父表相同名称和数据类型的列。子表还必须包括和父表相同的检查约束和检查表达式。相似地，一个继承链接也可以使用ALTER TABLE的NO INHERIT变体从一个子表中移除。动态增加和移除继承链接可以用于实现表划分（见第 5.11 节）。 一种创建一个未来将被用做子女的新表的方法是在CREATE TABLE中使用LIKE子句。这将创建一个和源表具有相同列的新表。如果源表上定义有任何CHECK约束，LIKE的INCLUDING CONSTRAINTS选项可以用来让新的子表也包含和父表相同的约束。 当有任何一个子表存在时，父表不能被删除。当子表的列或者检查约束继承于父表时，它们也不能被删除或修改。如果希望移除一个表和它的所有后代，一种简单的方法是使用CASCADE选项删除父表（见第 5.14 节）。 ALTER TABLE将会把列的数据定义或检查约束上的任何变化沿着继承层次向下传播。同样，删除被其他表依赖的列只能使用CASCADE选项。ALTER TABLE对于重名列的合并和拒绝遵循与CREATE TABLE同样的规则。 继承的查询仅在附表上执行访问权限检查。例如，在cities表上授予UPDATE权限也隐含着通过cities访问时在capitals表中更新行的权限。 这保留了数据（也）在父表中的样子。但是如果没有额外的授权，则不能直接更新capitals表。 以类似的方式，父表的行安全性策略（见第 5.8 节）适用于继承查询期间来自于子表的行。 只有当子表在查询中被明确提到时，其策略（如果有）才会被应用，在那种情况下，附着在其父表上的任何策略都会被忽略。 外部表（见第 5.12 节）也可以是继承层次 中的一部分，即可以作为父表也可以作为子表，就像常规表一样。如果 一个外部表是继承层次的一部分，那么任何不被该外部表支持的操作也 不被整个层次所支持。 警告 注意并非所有的SQL命令都能工作在继承层次上。用于数据查询、数据修改或模式修改（例如SELECT、UPDATE、DELETE、大部分ALTER TABLE的变体，但INSERT或ALTER TABLE ... RENAME不在此列）的命令会默认将子表包含在内并且支持ONLY记号来排除子表。负责数据库维护和调整的命令（如REINDEX、VACUUM）只工作在独立的、物理的表上并且不支持在继承层次上的递归。每个命令相应的行为请参见它们的参考页（SQL 命令）。 继承特性的一个严肃的限制是索引（包括唯一约束）和外键约束值应用在单个表上而非它们的继承子女。在外键约束的引用端和被引用端都是这样。因此，按照上面的例子： 如果我们声明cities.name为UNIQUE或者PRIMARY KEY，这将不会阻止capitals表中拥有和cities中城市同名的行。而且这些重复的行将会默认显示在cities的查询中。事实上，capitals在默认情况下是根本不能拥有唯一约束的，并且因此能够包含多个同名的行。我们可以为capitals增加一个唯一约束，但这无法阻止相对于cities的重复。 相似地，如果我们指定cities.nameREFERENCES某个其他表，该约束不会自动地传播到capitals。在此种情况下，我们可以变通地在capitals上手工创建一个相同的REFERENCES约束。 指定另一个表的列REFERENCES cities(name)将允许其他表包含城市名称，但不会包含首府名称。这对于这个例子不是一个好的变通方案。 某些未为继承层次结构实现的功能是为声明性分区实现的。在决定使用旧继承进行分区是否对应用程序有用时，需要非常小心。 原文参考：https://www.xmmup.com/2021/06/23/1076/
