openGauss 2.0.1 分区表

本文适用于openGauss 2.0.1版本(对应postgresql 9.2.4)，可以用select version();查看版本。

一、创建分区表

注：分区键必须在主键中。

varchar(8) 日期类型

create table {table_name}
(
    field1 varchar(32) not null,
    field2 varchar(8) not null,
    field3 numeric(20, 2) not null,
    field4 char(1),
    field5 numeric(9),
    field timestamp(3) not null,
    constraint {table_name}_pkey primary key (field1, field2)
) partition by range (field2) (
    partition p202201 values less than('20220201'),
    partition p202202 values less than('20220301')
);

timestamp 时间戳类型

create table {table_name}
(
    field1 varchar(32) not null,
    field2 timestamp not null,
    field3 numeric(20, 2) not null,
    field4 char(1),
    field5 numeric(9),
    constraint {table_name}_pkey primary key (field1, field2)
) partition by range (field2) (
    partition p202201 values less than(to_date('20220201', 'yyyymmdd')),
    partition p202202 values less than(to_date('20220301', 'yyyymmdd'))
);

二、创建分区索引

create index idx_{table_name}_field2 on {table_name} (field2) local;

三、查询指定分区

select * from {table_name} partition (p202201);

四、添加分区

varchar(8) 日期类型

alter table {table_name} add partition p202203 values less than('20220401');

timestamp 时间戳类型

alter table {table_name} add partition p202203 values less than(to_date('20220401', 'yyyymmdd'));

五、删除分区

alter table {table_name} drop partition p202201;

六、查询所有分区

select b.relname, a.relname as relname_par, a.boundaries
from pg_partition a join pg_class b on (a.parentid = b.oid)
where a.parttyppe = 'p' and b.relname = '{table_name}'
order by relname_par;

七、查询建表语句

select * from pg_catalog.pg_get_tabledef('{table_name}');

八、分区分裂

将分区p21000101在'20230224'左右切分成两块，然后重命名

alter table {table_name} split partition p21000101 at ('20230224') into (partition p20230224, partition p21000102);
alter table {table_name} rename partition p21000102 to p21000101;