9、表继承、临时表、表分区和表存储属性

这一章节里继续讨论表的内容，毕竟后面再高深的内容都是建立在处理表数据的基础之上的。

表继承

表继承其实是属于表创建相关的内容，但是如果这些内容都放在表创建那一部分，就显得有点太长了，看的心烦，就在这里单独来讲讲。表继承，从字面意思上来看，就是从其他位置继承一些东西过来，实际应用上也是这样，表继承就是以其他表为模板来生成新的表。基本命令格式如下：
create table table_child (like table_father);
我们来看一个示例：

postgres=# \d testdb1; 
                        Table "public.testdb1"
  Column   |         Type          |             Modifiers             
-----------+-----------------------+-----------------------------------
 id        | integer               | not null
 comments  | character varying(20) | default 'test'::character varying
 parent_id | integer               | 
Indexes:
    "testdb1_pkey" PRIMARY KEY, btree (id)

postgres=# select * from testdb1; 
 id | comments | parent_id 
----+----------+-----------
  1 | my test  |         5
(1 row)

上面是一张表的结构以及表中的数据，我们以这张表为模板生成一张新表testdb2：

postgres=# create table testdb2 (like testdb1); 
CREATE TABLE
postgres=# \d testdb2; 
            Table "public.testdb2"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 id        | integer               | not null
 comments  | character varying(20) | 
 parent_id | integer               | 

postgres=# select * from testdb2; 
 id | comments | parent_id 
----+----------+-----------
(0 rows)

从上面的代码中我们可以看到，生成的表testdb2和testdb1的结构一模一样，但是默认情况下没有继承表testdb1中的数据以及comments字段的默认值。通过查询官网文档发现，如果想继承原表的各种属性以及数据，需要用到including和with关键字，用法分别是：

including defaults 继承默认值
including constraint 继承约束
including indexes 继承索引
including storage 继承存储
including comments 继承注释
including all 继承原表的所有属性
create table table_child as select * from table_father [with no data];

我们来尝试一下，分别来生成一个继承默认值和一个继承原表所有属性的新表。

postgres=# create table testdb3 (like testdb1 including defaults); 
CREATE TABLE
postgres=# \d testdb3 
                        Table "public.testdb3"
  Column   |         Type          |             Modifiers             
-----------+-----------------------+-----------------------------------
 id        | integer               | not null
 comments  | character varying(20) | default 'test'::character varying
 parent_id | integer               | 

postgres=# select * from testdb3; 
 id | comments | parent_id 
----+----------+-----------
(0 rows)

从上面可以看到继承了默认值，没有继承表中数据以及主键索引

postgres=# create table testdb4 (like testdb1 including all); 
CREATE TABLE
postgres=# \d testdb4;
                        Table "public.testdb4"
  Column   |         Type          |             Modifiers             
-----------+-----------------------+-----------------------------------
 id        | integer               | not null
 comments  | character varying(20) | default 'test'::character varying
 parent_id | integer               | 
Indexes:
    "testdb4_pkey" PRIMARY KEY, btree (id)

postgres=# select * from testdb4;
 id | comments | parent_id 
----+----------+-----------
(0 rows)

从上面可以看到，including all关键字会继承所有属性，但是不会继承原表的数据。

postgres=# create table testdb5 as select * from testdb1;
SELECT 1
postgres=# \d testdb5; 
            Table "public.testdb5"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 id        | integer               | 
 comments  | character varying(20) | 
 parent_id | integer               | 

postgres=# select * from testdb5; 
 id | comments | parent_id 
----+----------+-----------
  1 | my test  |         5
(1 row)

这个示例使用as关键字创建新表testdb5，不带with子句。从中可以看到默认情况下，as语句会将原来表里的数据复制到新表中，但是不会复制原来表中的属性，例如各种索引、约束等。那能不能通过把这两种命令形式结合起来，既可以继承原表结构，又可以复制原表数据呢？目前还是没有这种命令的，只能通过完整的复制数据，再添加索引、约束等，或者先复制完整的表结构，再导入数据。

临时表

PostgreSQL支持两种类型的临时表，分别是会话级别临时表和事务级别临时表。同时，表中的数据也分为两个级别，一个是会话级别，一个是事务级别。因此就存在三种形式的临时表。分别是：

会话级别表、会话级别数据
会话级别表、事务级别数据
事务级别表、事务级别数据

第一种表和数据在会话周期都存在，会话销毁，表和数据销毁。第二种表在会话周期存在，数据在事务周期存在，事务周期结束数据销毁，会话结束表销毁。第三种表和数据在事务结束以后都会销毁。

临时表的创建
临时表的创建和普通表的创建唯一的区别就是在创建语句中加了一个temporary关键字（也可以简写为temp），基本命令格式如下：
create temporary table table_name (field1 type1, field2 type...);
也可以使用我们上面表继承的语法来创建临时表：
create temp table table_name(like table1);
或
create temp table table_name as select * from table1;
根据前面说的，临时表是分成三种类型的，而采用上面的语句，默认情况下创建的是会话级别的表和数据。而如果想创建其他级别的表，则需要使用下面几个子句：

on commit perserve rows 这个子句和默认情况相同，创建会话级别的表和数据，插入的数据保留到。
on commit delete rows 这个子句用来创建会话级别表和事务级别数据，事务销毁后表中数据销毁。
on commit drop 这个子句只能用在事务中创建临时表，在会话中创建只会显示表格创建的提示消息，但是看不到表格，因为表格一创建成功就立即销毁了。

通过下面几个示例来看一下：

postgres=# create temp table temp_1 as select * from testdb1; 
SELECT 1
postgres=# \d 
           List of relations
  Schema   |  Name   | Type  |  Owner   
-----------+---------+-------+----------
 pg_temp_2 | temp_1  | table | postgres
 public    | testdb1 | table | postgres
 public    | testdb2 | table | postgres
(3 rows)
postgres=# create temp table temp_2 as select id, parent_id from testdb1; 
SELECT 1
postgres=# \d temp_2;
    Table "pg_temp_2.temp_2"
  Column   |  Type   | Modifiers 
-----------+---------+-----------
 id        | integer | 
 parent_id | integer | 

postgres=# create temp table temp_3 (like testdb1); 
CREATE TABLE
postgres=# \d temp_3; 
           Table "pg_temp_2.temp_3"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 id        | integer               | not null
 comments  | character varying(20) | 
 parent_id | integer               |

postgres=# \d
           List of relations
  Schema   |  Name   | Type  |  Owner   
-----------+---------+-------+----------
 pg_temp_2 | temp_1  | table | postgres
 pg_temp_2 | temp_2  | table | postgres
 pg_temp_2 | temp_3  | table | postgres
 public    | testdb1 | table | postgres
 public    | testdb2 | table | postgres
(5 rows)

从上面可以看到，三张临时表都是处在一个特殊的模式下，模式名称是pg_temp_2。而在postgresql中，每个会话中创建的临时表都是处在特殊的模式下，模式名称一般都是pg_temp_x，x是根据不同的会话来分配的数字。我们来尝试创建一下其他级别的表：

创建会话级别临时表和事务级别数据。

postgres=# create temp table temp_4 (like temp_1 including all) on commit delete rows; 
CREATE TABLE

postgres=# select * from temp_4; 
 id | comments | parent_id 
----+----------+-----------
(0 rows)

postgres=# insert into temp_4 values(1, 'test4', 4); 
INSERT 0 1
postgres=# select * from temp_4; 
 id | comments | parent_id 
----+----------+-----------
(0 rows)

postgres=# begin;
BEGIN
postgres=# insert into temp_4 values(1, 'test4', 4);
INSERT 0 1
postgres=# select * from temp_4; 
 id | comments | parent_id 
----+----------+-----------
  1 | test4    |         4
(1 row)

postgres=# end; 
COMMIT
postgres=# select * from temp_4; 
 id | comments | parent_id 
----+----------+-----------
(0 rows)

上面的示例中，我们操作依次是：
（1）创建临时表temp_4，表是会话级别，数据是事务级别
（2）查看temp_4表，没有任何数据
（3）插入一条数据，再看表，还是没有数据
（4）begin启动一个事务，往表中插入数据，查看表，发现有一条数据
（5）end结束事务，再查看表，表中数据消失。
示例清楚地解释了这种临时表的特性

事务临时表和事务数据

postgres=# create temp table temp_5 (like temp_1 including all) on commit drop; 
CREATE TABLE
postgres=# \d 
           List of relations
  Schema   |  Name   | Type  |  Owner   
-----------+---------+-------+----------
 pg_temp_2 | temp_1  | table | postgres
 pg_temp_2 | temp_2  | table | postgres
 pg_temp_2 | temp_3  | table | postgres
 pg_temp_2 | temp_4  | table | postgres
 public    | testdb1 | table | postgres
 public    | testdb2 | table | postgres
(6 rows)

上面代码中我们创建了一个事务级别表，提示创建成功，但是看不到这个临时表。说明事务级别表在会话中没法存在，或者说创建之后就销毁。我们在事务中再来看：

postgres=# begin; 
BEGIN
postgres=# create temp table temp_5 (like temp_1 including all) on commit drop; 
CREATE TABLE
postgres=# \d 
           List of relations
  Schema   |  Name   | Type  |  Owner   
-----------+---------+-------+----------
 pg_temp_2 | temp_1  | table | postgres
 pg_temp_2 | temp_2  | table | postgres
 pg_temp_2 | temp_3  | table | postgres
 pg_temp_2 | temp_4  | table | postgres
 pg_temp_2 | temp_5  | table | postgres
 public    | testdb1 | table | postgres
 public    | testdb2 | table | postgres
(7 rows)

postgres=# select * from temp_5; 
 id | comments | parent_id 
----+----------+-----------
(0 rows)

postgres=# insert into temp_5 values(1, 'test5', 5); 
INSERT 0 1
postgres=# select * from temp_5; 
 id | comments | parent_id 
----+----------+-----------
  1 | test5    |         5
(1 row)

postgres=# end; 
COMMIT
postgres=# \d
           List of relations
  Schema   |  Name   | Type  |  Owner   
-----------+---------+-------+----------
 pg_temp_2 | temp_1  | table | postgres
 pg_temp_2 | temp_2  | table | postgres
 pg_temp_2 | temp_3  | table | postgres
 pg_temp_2 | temp_4  | table | postgres
 public    | testdb1 | table | postgres
 public    | testdb2 | table | postgres
(6 rows)

上面的代码是一个完整的事务，代码功能依次是：
（1）在事务中先创建临时表temp_5，查看表，确实存在。
（2）查看表中没有任何数据，往表中插入一条数据，再看表，有一条数据
（3）退出事务，再看表信息，temp_5这个临时表已经消失了。
相信经过这几个例子，大家对临时表的特性已经有所了解了。

表分区

表分区是通过某种标准将一个大表划分成若干个小表，然后创建触发器，在插入数据的时候，触发器根据指定的标准，将数据插入到不同的小表中去。可以提高性能，以及查询速度。标准可以是日期、时间、地区等等。
表分区的实现依赖于表继承，这里的表继承和上面所说的表继承略有区别，上面的表继承是通过复制父表的结构生成子表，而这里的表继承，指的是完全继承父表的表结构，同时还可以添加自己的字段来生成一张新的子表。如下所示：

postgres=# create table father(name text, age int); 
CREATE TABLE
postgres=# create table child(language text) inherits (father); 
CREATE TABLE
postgres=# \d father;
    Table "public.father"
 Column |  Type   | Modifiers 
--------+---------+-----------
 name   | text    | 
 age    | integer | 
Number of child tables: 1 (Use \d+ to list them.)

postgres=# \d child; 
      Table "public.child"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 name     | text    | 
 age      | integer | 
 language | text    | 
Inherits: father

在上面的示例种，子表child继承父表father，同时添加了自己字段language。

而且这种表继承还具有不同的特性，这种类型的表继承中，查询父表，可以查询到所有子表中对应字段的内容。我们通过几个例子来看一下这种表继承的特性：

postgres=# select * from father; 
 name | age 
------+-----
(0 rows)

postgres=# slect * from child 
postgres-# ; 
ERROR:  syntax error at or near "slect"
LINE 1: slect * from child 
        ^
postgres=# select * from child; 
 name | age | language 
------+-----+----------
(0 rows)

postgres=# insert into child values('张三', 16, '中文'); 
INSERT 0 1
postgres=# insert into child values('tom', 19, 'English');
INSERT 0 1
postgres=# select * from father; 
 name | age 
------+-----
 张三 |  16
 tom  |  19
(2 rows)

postgres=# select * from child; 
 name | age | language 
------+-----+----------
 张三 |  16 | 中文
 tom  |  19 | English
(2 rows)

从上面的例子中可以看到，往子表中插入的数据，在父表中可以看到对应字段的数据。再看下面这个例子：

postgres=# insert into father values('李四', 25);
INSERT 0 1
postgres=# select * from father; 
 name | age 
------+-----
 李四 |  25
 张三 |  16
 tom  |  19
(3 rows)

postgres=# select * from child; 
 name | age | language 
------+-----+----------
 张三 |  16 | 中文
 tom  |  19 | English
(2 rows)

可以看到，往父表中插入的数据，在子表中却看不到。

而这种类型的表继承的特性恰好是表分区实现的基础，可以通过查询主表来查询主表下所有分区表的内容。往分区表里插入数据，相当于是往主表中插入数据。在这里，主表相当于是子表，分区表相当于是父表。同时，还要通过触发器来实现往主表里插入数据或查询数据时，自动转到对应的分区表上。因此还要了解触发器的创建规则。因此这部分内容在学完触发器的知识后再来看。

表的存储属性

表的存储属性指的是
TOAST用于存储大字段的技术，在理解这个技术之前，先了解一下页的概念。页在PostgreSQL中是数据在文件存储中的单位，其大小是固定的且只能在编译时指定，之后无法修改，默认的大小为8 KB 。同时，PG 不允许一行数据跨页存储，那么对于超长的行数据，PG 就会启动 TOAST ，具体就是采用压缩和切片的方式。如果启用了切片，实际上行数据存储在另一张系统表的多个行中，这张表就叫 TOAST 表，这种存储方式叫行外存储。
而一般情况下，只有变长类型才会支持TOAST技术，在变长类型中，前4个字节是长度字。长度字的前2位是标志位，后30位是长度值。长度值包含自身占用的4个字节，因此，TOAST数据类型的长度最大应该是30b，及1GB（2^30-1）个字节。
前2位标志位，第一个表示是否压缩，第二个表示是否是行外存储。两个都是0表示不压缩，不是行外存储。第一个为1表示压缩过，使用之前必须解压。第二个为1表示行外存储，此时，长度字后面存储的只是一个指针。不管数据是否压缩，长度位后30bit都表示的是数据的真实大小，而不会是压缩以后的大小。
如果一张表A有对应的toast表，那么在系统表pg_class里面就会有一个表名A对应一个toast表的OID，然后根据这个oid就能找到对应的toast表pg_toast_oid。如果么某个大字段的数据存放在toast表中，那么这个字段的数据就会被切片成页面大小1/4大小的块。然后将这些数据块存放在toast表中。每个toast表有chunk_id、chunk_seq和chunk_data字段，数据块就放在chunk_data字段中。在chunk_id和chunk_seq上有一个唯一的索引，用于对数据的快速检索。因此在前面变长类型的字段的长度位中，如果存储的是指针的话，那么该指针就包含toast表的OID和特定数值的chunk_id，以及数据的实际长度。

在 PG 中每个表字段有四种 TOAST 的策略：

PLAIN ：避免压缩和行外存储。只有那些不需要 TOAST 策略就能存放的数据类型允许选择（例如 int 类型），而对于 text 这类要求存储长度超过页大小的类型，是不允许采用此策略的。
EXTENDED ：允许压缩和行外存储。一般会先压缩，如果还是太大，就会行外存储
EXTERNA ：允许行外存储，但不许压缩。类似字符串这种会对数据的一部分进行操作的字段，采用此策略可能获得更高的性能，因为不需要读取出整行数据再解压。
MAIN ：允许压缩，但不许行外存储。不过实际上，为了保证过大数据的存储，行外存储在其它方式（例如压缩）都无法满足需求的情况下，作为最后手段还是会被启动。因此理解为：尽量不使用行外存储更贴切。

在PG中，会为每个允许TOAST的字段设置一个默认的策略。但是可以使用ALTER TABLE SET STORAGE命令来修改系统设置的默认策略。

默认情况下，只有当一个字段数据的大小超过页大小的1/4的时候，才会触发压缩策略。即对于8k页大小的数据库来说，字段数据大小超过2k时才会触发压缩策略。在这种情况下，如果两张结构相同的表A和B，A中某个字段存储的数据小于2k，而B中该字段的数据大于2k，会出现B表所占用的空间反而小于A表。

从上面这些内容来看，所谓的存储属性，应该指的就是针对字段设置的各种策略。此外，还可以针对表设置fillfactor和toast.fillfactor这两个值。这两个值表示表的填充因子和toast表的填充因子，填充因子表示的是一个数据块的填充比例，即一个数据库填充到多少以后就不填充数据了，剩下的空间留做数据更新时使用。
在PG中，更新旧数据时，不会删除旧数据，而是在保留的空间里写入一条新数据，然后在旧数据和新数据之间创建一个链表，这样就不用更新索引，索引找到旧数据时，会根据链表找到新数据。但是如果填充因子设置的过大，更新旧数据时，保留的空间不足以存放更新的数据，且数据不能跨页存放，因此必须放在新的数据块，此时旧需要更新索引。会更耗时，因此，对于数据更新频繁的表，这个值可以设置的小一点。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,332评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,508评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,812评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,607评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,728评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,919评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,071评论 3赞 410
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,802评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,256评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,576评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,712评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,389评论 4赞 332
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,032评论 3赞 316
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,798评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,026评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,473评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,606评论 2赞 350

9、表继承、临时表、表分区和表存储属性

表继承

临时表

表分区

表的存储属性

推荐阅读更多精彩内容