Postgresql从继承到分区
(pg_partman)
Postgresql从继承到分区（一）
http://my.oschina.net/lianshunke/blog/205296
Postgresql从继承到分区（二）
http://my.oschina.net/lianshunke/blog/205296
Postgresql从继承到分区（三）
http://my.oschina.net/lianshunke/blog/205316
一、Inheritance
http://www.postgresql.org/docs/9.3/static/ddl-inherit.html
通过下面的简单例子说明什么是表继承：
1.1 单表继承
postgres=#CREATEDATABASElsk;
CREATEDATABASE
postgres=#\clsk
Youarenowconnectedtodatabase"lsk"asuser"postgres".
lsk=#CREATETABLEt1(idint,namevarchar(20));
CREATETABLE
lsk=#CREATETABLEt2(ageint)inherits(t1);
CREATETABLE
{在子表中新添的字段会汇同父表的所有字段一起作为子表字段}
lsk=#\dt1 Table"public.t1" Column|Type|Modifiers --------+-----------------------+----------- id|integer| name|charactervarying(20)| Numberofchildtables:1(Use\d+tolistthem.) lsk=#\dt2 Table"public.t2" Column|Type|Modifiers --------+-----------------------+----------- id|integer| name|charactervarying(20)| age|integer| Inherits:t1
插入数据：
lsk=#INSERTINTOt1VALUES(1,'lian1'); INSERT01 lsk=#INSERTINTOt1VALUES(2,'lian2'); INSERT01 lsk=#INSERTINTOt2VALUES(3,'lian3',11); INSERT01 lsk=#INSERTINTOt2VALUES(4,'lian4',12); INSERT01
查看表结果：
lsk=#SELECT*fromt1;
id|name
----+-------
1|lian1
2|lian2
3|lian3
4|lian4
(4rows)
{从父表中查询将显示父表及子表的所有数据}
lsk=#SELECT*fromt2;
id|name|age
----+-------+-----
3|lian3|11
4|lian4|12
(2rows)
{从子表中查询只显示子表中的数据}
lsk=#SELECT*fromonlyt1;
id|name
----+-------
1|lian1
2|lian2
(2rows)
{通过ONLY关键字实现只对父表的查询}
1.2 多表继承
续1.1节内容
1.2.1 级联继承
创建t3表继承上节子表t2：
lsk=#CREATETABLEt3()inherits(t2); CREATETABLE lsk=#\dt3 Table"public.t3" Column|Type|Modifiers --------+-----------------------+----------- id|integer| name|charactervarying(20)| age|integer| Inherits:t2
插入测试数据：
lsk=#INSERTINTOt3VALUES(5,'lian5',13); INSERT01
lsk=#SELECT*fromt1; id|name ----+------- 1|lian1 2|lian2 3|lian3 4|lian4 5|lian5 (5rows) lsk=#SELECT*fromt2; id|name|age ----+-------+----- 3|lian3|11 4|lian4|12 5|lian5|13 (3rows) lsk=#SELECT*fromt3; id|name|age ----+-------+----- 5|lian5|13 (1row)
1.2.2 并行继承
创建t4表继承顶层父表t1：
lsk=#CREATETABLEt4()inherits(t1);
CREATETABLE
lsk=#\dt4
Table"public.t4"
Column|Type|Modifiers
--------+-----------------------+-----------
id|integer|
name|charactervarying(20)|
Inherits:t1
lsk=#\d+t1
Table"public.t1"
Column|Type|Modifiers|Storage|Description
--------+-----------------------+-----------+----------+-------------
id|integer||plain|
name|charactervarying(20)||extended|
Childtables:t2,t4
HasOIDs:no
lsk=#INSERTINTOt4VALUES(6,'lian6');
INSERT01
lsk=#SELECT*fromt1;
id|name
----+-------
1|lian1
2|lian2
3|lian3
4|lian4
6|lian6
5|lian5
(6rows)
{可以看出查询是按照分层顺序查询，即先并行再级联}
lsk=#SELECT*fromt4; id|name ----+------- 6|lian6 (1row)
1.3 查看数据来源
lsk=#SELECTp.relname,c.*FROMt1c,pg_classpWHEREc.tableoid=p.oid; relname|id|name ---------+----+------- t1|1|lian1 t1|2|lian2 t2|3|lian3 t2|4|lian4 t4|6|lian6 t3|5|lian5 (6rows)
