Postgresql Partition Table One

Postgres 10 new Feature partition table

http://francs3.blog.163.com/blog/static/40576727201742103158135/

Postgres 10 before partition table plug-in implementation Pg_pathman

Https://yq.aliyun.com/articles/62314#25

Here simple records are based on triggers or rule implementations

Create a partitioned table
CREATE table RECORD (--Main Table
record_id int not NULL,
Record_pro Text Not NULL,
city_id int,
City_name text
);

CREATE TABLE Record_defalut (CHECK (city_id = 0)) INHERITS (RECORD); --default
CREATE TABLE record_bj (CHECK (city_id = 1)) INHERITS (RECORD); --Beijing
CREATE TABLE record_sh (CHECK (city_id = 2)) INHERITS (RECORD); --Shanghai
CREATE TABLE Record_gz (CHECK (city_id = 3)) INHERITS (RECORD); --Guangzhou
CREATE TABLE RECORD_SZ (CHECK (city_id = 4)) INHERITS (RECORD); --Shenzhen

Create a rule

CREATE RULE Record_default_rule_insert as
On INSERT to RECORD WHERE city_id = 0
Do INSTEAD
INSERT into Record_defalut VALUES (new.*);

CREATE RULE Record_bj_rule_insert as
On INSERT to RECORD WHERE city_id = 1
Do INSTEAD
INSERT into RECORD_BJ VALUES (new.*);

CREATE RULE Record_sh_rule_insert as
On INSERT to RECORD WHERE city_id = 2
Do INSTEAD
INSERT into Record_sh VALUES (new.*);

CREATE RULE Record_gz_rule_insert as
On INSERT to RECORD WHERE city_id = 3
Do INSTEAD
INSERT into Record_gz VALUES (new.*);

CREATE RULE Record_sz_rule_insert as
On INSERT to RECORD WHERE city_id = 4
Do INSTEAD
INSERT into RECORD_SZ VALUES (new.*);

Input analog data

Truncate RECORD;

INSERT into RECORD
SELECT generate_series (1, +), ' Recode ', (Int4 (random () *10))%5, ' City ';

INSERT into RECORD VALUES (1, ' Recode ', 3, ' City ');
INSERT into RECORD VALUES (2, ' Recode ', 3, ' City ');
INSERT into RECORD VALUES (3, ' Recode ', 0, ' city ');
INSERT into RECORD VALUES (4, ' Recode ', 0, ' city ');
INSERT into RECORD VALUES (5, ' Recode ', 4, ' City ');
INSERT into RECORD VALUES (6, ' Recode ', 3, ' City ');
INSERT into RECORD VALUES (7, ' Recode ', 3, ' City ');
INSERT into RECORD VALUES (8, ' Recode ', 0, ' city ');
INSERT into RECORD VALUES (9, ' Recode ', 0, ' city ');
INSERT into RECORD VALUES ("Recode", 2, ' City ');

Postgresql Partition Table One