1.主表创建
create table sspudb_rh1
(
id numeric(20,0) NOT NULL,
name character varying(20) NOT NULL,
age numeric(10,0) NOT NULL,
sex character varying(10) NOT NULL,
cardid numeric(30,0) NOT NULL,
joindate timestamp without time zone NOT NULL,
region character varying(12) NOT NULL,
tel character varying(12) NOT NULL,
email character varying(30) NOT NULL,
recommend character varying(10),
identifier character varying(100),
primary key (id,joindate)
)
PARTITION BY RANGE(joindate);
2.子表创建
--一级子分区创建
create table sspudb_rh1_phis partition OF sspudb_rh1 for values from (MINVALUE) TO ('2010-01-01 00:00:00') PARTITION BY HASH (id);
create table sspudb_rh1_p2010 partition of sspudb_rh1 for values from ('2010-01-01 00:00:00') to ('2011-01-01 00:00:00') PARTITION BY HASH (id);
create table sspudb_rh1_p2011 partition of sspudb_rh1 for values from ('2011-01-01 00:00:00') to ('2012-01-01 00:00:00') PARTITION BY HASH (id);
create table sspudb_rh1_pother partition of sspudb_rh1 for values from ('2012-01-01 00:00:00') to (MAXVALUE) PARTITION BY HASH (id);
3.创建二级hash子分区
--sspudb_rh1_phis 分区建两个子分区
CREATE TABLE sspudb_rh1_phis_1 PARTITION OF sspudb_rh1_phis FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE sspudb_rh1_phis_2 PARTITION OF sspudb_rh1_phis FOR VALUES WITH (modulus 2, remainder 1);
--sspudb_rh1_p2010 分区建两个子分区
CREATE TABLE sspudb_rh1_p2010_1 PARTITION OF sspudb_rh1_p2010 FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE sspudb_rh1_p2010_2 PARTITION OF sspudb_rh1_p2010 FOR VALUES WITH (modulus 2, remainder 1);
--sspudb_rh1_p2011 分区建两个子分区
CREATE TABLE sspudb_rh1_p2011_1 PARTITION OF sspudb_rh1_p2011 FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE sspudb_rh1_p2011_2 PARTITION OF sspudb_rh1_p2011 FOR VALUES WITH (modulus 2, remainder 1);
--sspudb_rh1_pother 分区建两个子分区
CREATE TABLE sspudb_rh1_pother_1 PARTITION OF sspudb_rh1_pother FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE sspudb_rh1_pother_2 PARTITION OF sspudb_rh1_pother FOR VALUES WITH (modulus 2, remainder 1);
4.创建全局索引
CREATE INDEX idx_sspudb_rh1_joindate_id ON sspudb_rh1 USING BTREE(joindate,id);
sspudb=# insert into sspudb_rh1 select * from sspudb_r1;
INSERT 0 8
sspudb=# select * from sspudb_rh1;
id | name | age | sex | cardid | joindate | region | tel | email | recommend | identifier
----+------+-----+------+--------------------+---------------------+--------+-------------+-----------------+-----------+---------------
1 | xsq1 | 18 | male | 622722199009121121 | 2010-10-01 10:10:10 | 北京 | 13651254654 | sspudb@163.com | xsq | first_people
2 | xsq3 | 18 | male | 622722199003121121 | 2011-10-01 10:10:10 | 北京1 | 13641254654 | sspudb1@163.com | xsq1 | first_people1
4 | xsq3 | 14 | male | 622722199403121121 | 2014-10-01 10:10:10 | 北京4 | 13641254654 | sspudb4@163.com | xsq1 | first_people1
7 | xsq3 | 17 | male | 622722199703121121 | 2017-10-01 10:10:10 | 北京7 | 13647254654 | sspudb7@163.com | xsq1 | first_people1
3 | xsq3 | 19 | male | 622722199033121121 | 2012-10-01 10:10:10 | 北京3 | 13641254634 | sspudb3@163.com | xsq3 | first_people1
5 | xsq3 | 15 | male | 622722199503121121 | 2015-10-01 10:10:10 | 北京5 | 13645254654 | sspudb1@163.com | xsq1 | first_people1
6 | xsq6 | 16 | male | 622722199603121121 | 2016-10-01 10:10:10 | 北京6 | 13641256654 | sspudb6@163.com | xsq6 | first_people1
8 | xsq8 | 18 | male | 622722199803121121 | 2018-10-01 10:10:10 | 北京8 | 13648254654 | sspudb8@163.com | xsq1 | first_people1
(8 rows)
sspudb=# SELECT v.tableoid::regclass,count(v.*) FROM sspudb_rh1 v GROUP BY v.tableoid;
tableoid | count
---------------------+-------
sspudb_rh1_pother_2 | 4
sspudb_rh1_p2011_2 | 1
sspudb_rh1_pother_1 | 2
sspudb_rh1_p2010_2 | 1
(4 rows)
select
nmsp_parent.nspname as parent_schema,
parent.relname as parent,
nmsp_child.nspname as child_schema,
child.relname as child_schema
from pg_inherits
join pg_class parent on pg_inherits.inhparent = parent.oid
join pg_class child on pg_inherits.inhrelid = child.oid
join pg_namespace nmsp_parent on nmsp_parent.oid = parent.relnamespace
join pg_namespace nmsp_child on nmsp_child.oid = child.relnamespace
where parent.relname like 'sspudb_rh1%';
parent_schema | parent | child_schema | child_schema
---------------+-----------------------------------+--------------+-------------------------------------
public | sspudb_rh1 | public | sspudb_rh1_phis
public | sspudb_rh1_pkey | public | sspudb_rh1_phis_pkey
public | sspudb_rh1 | public | sspudb_rh1_p2010
public | sspudb_rh1_pkey | public | sspudb_rh1_p2010_pkey
public | sspudb_rh1 | public | sspudb_rh1_p2011
public | sspudb_rh1_pkey | public | sspudb_rh1_p2011_pkey
public | sspudb_rh1 | public | sspudb_rh1_pother
public | sspudb_rh1_pkey | public | sspudb_rh1_pother_pkey
public | sspudb_rh1_phis | public | sspudb_rh1_phis_1
public | sspudb_rh1_phis_pkey | public | sspudb_rh1_phis_1_pkey
public | sspudb_rh1_phis | public | sspudb_rh1_phis_2
public | sspudb_rh1_phis_pkey | public | sspudb_rh1_phis_2_pkey
public | sspudb_rh1_p2010 | public | sspudb_rh1_p2010_1
public | sspudb_rh1_p2010_pkey | public | sspudb_rh1_p2010_1_pkey
public | sspudb_rh1_p2010 | public | sspudb_rh1_p2010_2
public | sspudb_rh1_p2010_pkey | public | sspudb_rh1_p2010_2_pkey
public | sspudb_rh1_p2011 | public | sspudb_rh1_p2011_1
public | sspudb_rh1_p2011_pkey | public | sspudb_rh1_p2011_1_pkey
public | sspudb_rh1_p2011 | public | sspudb_rh1_p2011_2
public | sspudb_rh1_p2011_pkey | public | sspudb_rh1_p2011_2_pkey
public | sspudb_rh1_pother | public | sspudb_rh1_pother_1
public | sspudb_rh1_pother_pkey | public | sspudb_rh1_pother_1_pkey
public | sspudb_rh1_pother | public | sspudb_rh1_pother_2
public | sspudb_rh1_pother_pkey | public | sspudb_rh1_pother_2_pkey
public | sspudb_rh1_phis_joindate_id_idx | public | sspudb_rh1_phis_1_joindate_id_idx
public | sspudb_rh1_phis_joindate_id_idx | public | sspudb_rh1_phis_2_joindate_id_idx
public | sspudb_rh1_p2010_joindate_id_idx | public | sspudb_rh1_p2010_1_joindate_id_idx
public | sspudb_rh1_p2010_joindate_id_idx | public | sspudb_rh1_p2010_2_joindate_id_idx
public | sspudb_rh1_p2011_joindate_id_idx | public | sspudb_rh1_p2011_1_joindate_id_idx
public | sspudb_rh1_p2011_joindate_id_idx | public | sspudb_rh1_p2011_2_joindate_id_idx
public | sspudb_rh1_pother_joindate_id_idx | public | sspudb_rh1_pother_1_joindate_id_idx
public | sspudb_rh1_pother_joindate_id_idx | public | sspudb_rh1_pother_2_joindate_id_idx
(32 rows)