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)

http://my.oschina.net/lianshunke/blog/205316