合 PG中的表继承和only关键词
👉 本文共约3278个字，系统预计阅读时间或需13分钟。
官网：http://postgres.cn/docs/13/ddl-inherit.html
表继承简介
相似于程序语言的类继承，PostgreSQL的表也可以继承（Table Inheritance）。创建继承表的操作如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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全部列相同的列。
因为表中没有内容所以看不出端倪，先插入数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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，delete都支持only关键字。
第四个查询，和第1个查询一样。在表后面写一个*表示显式指定包括所有子表。写*并不是必须的，不过写在表后面 可以用于强调搜索额外的子表。
假使我们想知道某个行版本来自哪个表？在每个表里我们都有一个tableoid系统属性可以告诉你源表是谁：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
需要注意2点：
1、如果只想查询父表的数据，那么需要在父表名称前加上only关键词。
2、对于UPDATE、DELETE和SELECT操作，如果在父表前不加only关键词，则会对父表和所有子表进行DML操作。所以，对于使用了继承表的场景，对父表的DELETE和UPDATE操作需要谨慎，因为会对父表和所有子表的数据进行DML操作。