postgresql中的分区表

在早期的版本中pg中的分区表都是通过继承的方式创建的，通过继承的方式来创建分区表的这种方式用起来不是很方便。

myDB=# create table parent(id int primary key,name varchar(100));
CREATE TABLE
myDB=# create index idx_name on parent(name);
CREATE INDEX
myDB=# create table child(age int,sex int) inherits(parent);
CREATE TABLE
myDB=# \d child;
            Table "public.child"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 id     | integer                | not null
 name   | character varying(100) | 
 age    | integer                | 
 sex    | integer                | 
Inherits: parent

myDB=# \d parent;
            Table "public.parent"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 id     | integer                | not null
 name   | character varying(100) | 
Indexes:
    "parent_pkey" PRIMARY KEY, btree (id)
    "idx_name" btree (name)
Number of child tables: 1 (Use \d+ to list them.)

myDB=# insert into parent values(1,'aa');
INSERT 0 1
myDB=# select * from parent;
 id | name 
----+------
  1 | aa
(1 row)

myDB=# select * from child;
 id | name | age | sex 
----+------+-----+-----
(0 rows)

myDB=# insert into child values(1,'bb',1,1);
INSERT 0 1
myDB=# select * from parent;
 id | name 
----+------
  1 | aa
  1 | bb
(2 rows)

myDB=# select * from child;
 id | name | age | sex 
----+------+-----+-----
  1 | bb   |   1 |   1
(1 row)

分区表的例子

myDB=# create table t_parent(id int ,name varchar(100),c_time date);
CREATE TABLE
myDB=# create table t_part1(check(c_time>='2017-01-01' and c_time<='2017-02-01')) inherits(t_parent);
CREATE TABLE
myDB=# create table t_part2(check(c_time>='2017-02-01' and c_time<='2017-03-01')) inherits(t_parent);
CREATE TABLE

CREATE OR REPLACE FUNCTION t_parent_insert_trigger()                      
RETURNS TRIGGER AS $$  
BEGIN  
    IF ( NEW.c_time >= DATE '2017-01-01' AND    
         NEW.c_time <= DATE '2017-02-01' ) THEN  
        INSERT INTO t_part1 VALUES (NEW.*);  
    ELSIF ( NEW.c_time >DATE '2017-02-01' AND  
            NEW.c_time <= DATE '2017-03-01' ) THEN  
        INSERT INTO t_part2 VALUES (NEW.*);  
    ELSE  
        RAISE EXCEPTION 'Date out of range';  
    END IF;  
    RETURN NULL;  
END;  
$$  
LANGUAGE plpgsql;
create trigger insert_t_parent_trigger before insert on t_parent for each row execute procedure t_parent_insert_trigger ();
myDB=# insert into t_parent values(1,'aa',date '2017-01-02');
INSERT 0 0
myDB=# select * from t_part1;
 id | name |   c_time   
----+------+------------
  1 | aa   | 2017-01-02
(1 row)

myDB=# insert into t_parent values(1,'aa',date '2018-01-02');
ERROR:  Date out of range
CONTEXT:  PL/pgSQL function t_parent_insert_trigger() line 10 at RAISE


myDB=# update t_parent set name='bb' where id=1;
UPDATE 1
myDB=# select * from t_part1;
 id | name |   c_time   
----+------+------------
  1 | bb   | 2017-01-02
(1 row)


在删除分区的时候，倒是不用维护这个触发器
myDB=# drop table t_part1;
DROP TABLE
myDB=# insert into t_parent values(1,'aa',date '2017-02-02');

myDB=# select * from t_parent;
 id | name |   c_time   
----+------+------------
  1 | aa   | 2017-02-02
(1 row)

看到上面的这种方式处理起来不是很方便