在早期的版本中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)
看到上面的这种方式处理起来不是很方便