PostgreSQL数据库分区表创建与测试

zhouying23

目的：
创建分区表并插入数据，验证数据存放效果。


思路：
PG的分区表和Oracle与MySQL有所不同，是基于子表实现的分区表，即创建一个父表，然后创建子表，插入的数据会存放在子表中，但是增删改操作的语句写父表即可，数据分放的规则通过触发器、存储过程实现。

本次测试中共创建4个子表和1个父表，分别为rock、pop、jazz和classic4个子表和1个music父表，往里面插入歌曲信息，并验证是否自动存放到响应的分区内。




测试过程：

创建父表：                                            
music=> create table music(id int,name text,style text);
CREATE TABLE

创建子表：
music=> create table rock (check(style = 'rock')) inherits(music);
CREATE TABLE
music=> create table pop (check(style = 'pop')) inherits(music);
CREATE TABLE
music=> create table classic (check(style = 'classic')) inherits(music);
CREATE TABLE
music=> create table jazz (check(style = 'jazz')) inherits(music);
CREATE TABLE



查看表相关信息：
music=> \d
                关联列表
架构模式 |  名称   |  型别  |  拥有者  
----------+---------+--------+----------
public   | classic | 资料表 | eric
public   | jazz    | 资料表 | eric
public   | music   | 资料表 | eric
public   | pop     | 资料表 | eric
public   | rock    | 资料表 | eric
public   | test    | 资料表 | postgres
(6 行记录)

可以看到music有4个子表，分别为classic,jazz,pop和rock:
music=> \dS+ music
                 资料表 "public.music"
栏位  |  型别   | 修饰词 |   存储   | 统计目标 | 描述
-------+---------+--------+----------+----------+------
id    | integer |        | plain    |          |
name  | text    |        | extended |          |
style | text    |        | extended |          |
子表: classic,
      jazz,
      pop,
      rock


可以看到pop表的父表为music:
music=> \dS+ pop
                  资料表 "public.pop"
栏位  |  型别   | 修饰词 |   存储   | 统计目标 | 描述
-------+---------+--------+----------+----------+------
id    | integer |        | plain    |          |
name  | text    |        | extended |          |
style | text    |        | extended |          |
检查约束限制
    "pop_style_check" CHECK (style = 'pop'::text)
继承: music

为子表创建索引：
music=> create index music_pop_id on pop (id);
CREATE INDEX
music=> create index music_rock_id on rock (id);
CREATE INDEX
music=> create index music_jazz_id on jazz (id);
CREATE INDEX
music=> create index music_classic_id on classic (id);
CREATE INDEX


创建function：
music=> CREATE OR REPLACE FUNCTION music_insert_trigger()
music-> RETURNS TRIGGER AS
music-> $$
music$> BEGIN
music$>      IF (NEW.style = 'rock') THEN
music$>          INSERT INTO rock VALUES (NEW.*);
music$>      ELSEIF (NEW.style = 'pop') THEN
music$>          INSERT INTO pop VALUES (NEW.*);
music$>      ELSEIF (NEW.style = 'pop') THEN
music$>          INSERT INTO pop VALUES (NEW.*);
music$>      ELSEIF (NEW.style = 'jazz') THEN
music$>          INSERT INTO jazz VALUES (NEW.*);
music$>      ELSEIF (NEW.style = 'classic') THEN
music$>          INSERT INTO classic VALUES (NEW.*);
music$>      END IF;
music$>      RETURN NULL;
music$> END;
music$> $$
music-> LANGUAGE plpgsql ;
CREATE FUNCTION



创建触发器：
music=> CREATE TRIGGER insert_music_trigger
music->    BEFORE INSERT ON  music
music->     FOR EACH row EXECUTE PROCEDURE music_insert_trigger() ;
CREATE TRIGGER


插入数据：
music=> insert into music values(2,'Have a Nice Day','pop')
;
INSERT 0 0
music=> insert into music values(1,'21 Gun','rock')
;
INSERT 0 0



验证数据插入效果：


查询父表：
music=> select * from music;
id |      name       | style
----+-----------------+-------
  1 | 21 Gun          | rock
  2 | Have a Nice Day | pop
(2 行记录)

查询子表：
music=> select * from pop ;
id |      name       | style
----+-----------------+-------
  2 | Have a Nice Day | pop
(1 行记录)


music=> select * from rock;
id |  name  | style
----+--------+-------
  1 | 21 Gun | rock
(1 行记录)




不同的数据严格的按照规则存放进入了相应的表中。