【postgresql】range-range 复合范围分区表

最新推荐文章于 2024-10-05 12:07:50 发布

戒掉贪嗔痴(薛双奇)

最新推荐文章于 2024-10-05 12:07:50 发布

阅读量128

点赞数 1

分类专栏：数据库运维-PostgreSQL 文章标签： postgresql java 数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/141755156

版权

数据库运维-PostgreSQL 专栏收录该内容

49 篇文章 5 订阅

订阅专栏

1.创建主表

create table sspudb_rr1
(
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_rr1_phis partition OF sspudb_rr1 for values from (MINVALUE) TO ('2010-01-01 00:00:00') ;
create table sspudb_rr1_p2010 partition of sspudb_rr1 for values from ('2010-01-01 00:00:00') to ('2011-01-01 00:00:00') PARTITION BY RANGE (joindate);
create table sspudb_rr1_p2011 partition of sspudb_rr1 for values from ('2011-01-01 00:00:00') to ('2012-01-01 00:00:00') PARTITION BY RANGE (joindate);
create table sspudb_rr1_pother partition of sspudb_rr1 for values from ('2012-01-01 00:00:00') to (MAXVALUE);

3.创建二级子分区

--按季度分区
--sspudb_rr1_p2010 分区表的基础上再创建分区。
create table sspudb_rr1_p2010_q1 partition of sspudb_rr1_p2010 for values from ('2010-01-01 00:00:00')to ('2010-04-01 00:00:00');
create table sspudb_rr1_p2010_q2 partition of sspudb_rr1_p2010 for values from ('2010-04-01 00:00:00')to ('2010-07-01 00:00:00');
create table sspudb_rr1_p2010_q3 partition of sspudb_rr1_p2010 for values from ('2010-07-01 00:00:00')to ('2010-10-01 00:00:00');
create table sspudb_rr1_p2010_q4 partition of sspudb_rr1_p2010 for values from ('2010-10-01 00:00:00')to ('2011-01-01 00:00:00');

--sspudb_rr1_p2011 分区表的基础上再创建分区。
create table sspudb_rr1_p2011_q1 partition of sspudb_rr1_p2011 for values from ('2011-01-01 00:00:00')to ('2011-04-01 00:00:00');
create table sspudb_rr1_p2011_q2 partition of sspudb_rr1_p2011 for values from ('2011-04-01 00:00:00')to ('2011-07-01 00:00:00');
create table sspudb_rr1_p2011_q3 partition of sspudb_rr1_p2011 for values from ('2011-07-01 00:00:00')to ('2011-10-01 00:00:00');
create table sspudb_rr1_p2011_q4 partition of sspudb_rr1_p2011 for values from ('2011-10-01 00:00:00')to ('2012-01-01 00:00:00');

4.创建全局索引

CREATE INDEX idx_sspudb_rr1_joindate ON sspudb_rr1 USING BTREE(joindate);


sspudb=# \d+ sspudb_rr1
                                              Partitioned table "public.sspudb_rr1"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id         | numeric(20,0)               |           | not null |         | main     |             |              | 
 name       | character varying(20)       |           | not null |         | extended |             |              | 
 age        | numeric(10,0)               |           | not null |         | main     |             |              | 
 sex        | character varying(10)       |           | not null |         | extended |             |              | 
 cardid     | numeric(30,0)               |           | not null |         | main     |             |              | 
 joindate   | timestamp without time zone |           | not null |         | plain    |             |              | 
 region     | character varying(12)       |           | not null |         | extended |             |              | 
 tel        | character varying(12)       |           | not null |         | extended |             |              | 
 email      | character varying(30)       |           | not null |         | extended |             |              | 
 recommend  | character varying(10)       |           |          |         | extended |             |              | 
 identifier | character varying(100)      |           |          |         | extended |             |              | 
Partition key: RANGE (joindate)
Indexes:
    "sspudb_rr1_pkey" PRIMARY KEY, btree (id, joindate)
    "idx_sspudb_rr1_joindate" btree (joindate)
Partitions: sspudb_rr1_p2010 FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2011-01-01 00:00:00'), PARTITIONED,
            sspudb_rr1_p2011 FOR VALUES FROM ('2011-01-01 00:00:00') TO ('2012-01-01 00:00:00'), PARTITIONED,
            sspudb_rr1_phis FOR VALUES FROM (MINVALUE) TO ('2010-01-01 00:00:00'),
            sspudb_rr1_pother FOR VALUES FROM ('2012-01-01 00:00:00') TO (MAXVALUE)


sspudb=# insert into sspudb_rr1 select * from sspudb_r1;
INSERT 0 8

SELECT v.tableoid::regclass,count(v.*) FROM sspudb_rr1 v GROUP BY v.tableoid;
sspudb=# SELECT v.tableoid::regclass,count(v.*) FROM sspudb_rr1 v GROUP BY v.tableoid;
      tableoid       | count 
---------------------+-------
 sspudb_rr1_pother   |     6
 sspudb_rr1_p2011_q4 |     1
 sspudb_rr1_p2010_q4 |     1
(3 rows)