Postgre创建分区表

创建范围分区表

# 创建分区表 主表
CREATE TABLE person_1
(
    id SERIAL NOT NULL,
    name VARCHAR NOT NULL,
    begin_time TIMESTAMP,
    end_time TIMESTAMP
) PARTITION BY RANGE (begin_time);

# 创建分区表 子表
create table person_1_2024_06_23 
partition of person_1 for values from ('2024-06-23 00:00:00') to ('2024-06-24 00:00:00');
create table person_1_2024_06_24
partition of person_1 for values from ('2024-06-24 00:00:00') to ('2024-06-25 00:00:00');

# 往主表插入数据，PostgreSQL内部会自动管理数据插入的路由
insert into person_1 (id, "name", begin_time, end_time)
values(1,'张三','2024-06-23 01:00:00','2024-06-23 01:00:00'),
(2,'李四','2024-06-24 01:00:00','2024-06-24 01:00:00');

创建列表分区表

CREATE TABLE person_2
(
    id SERIAL NOT NULL,
    name VARCHAR NOT NULL,
    begin_time TIMESTAMP,
    end_time TIMESTAMP
) PARTITION BY LIST (name);

create table person_2_1
partition of person_2 for values in ('张三','李四');
create table person_2_2
partition of person_2 for values in ('王五','赵六');

insert into person_2 (id, "name", begin_time, end_time)
values(1,'张三','2024-06-23 01:00:00','2024-06-23 01:00:00'),
(2,'李四','2024-06-24 01:00:00','2024-06-24 01:00:00'),
(3,'王五','2024-06-23 01:00:00','2024-06-23 01:00:00'),
(4,'赵六','2024-06-23 01:00:00','2024-06-23 01:00:00');

创建哈希分区表

CREATE TABLE person_3
(
    id SERIAL NOT NULL,
    name VARCHAR NOT NULL,
    begin_time TIMESTAMP,
    end_time TIMESTAMP
) PARTITION BY hash (name);

create table person_3_1
partition of person_3 for values with (modulus 2, remainder 0);
create table person_3_2
partition of person_3 for values with (modulus 2, remainder 1);

insert into person_3 (id, "name", begin_time, end_time)
values(1,'张三','2024-06-23 01:00:00','2024-06-23 01:00:00'),
(2,'李四','2024-06-24 01:00:00','2024-06-24 01:00:00'),
(3,'王五','2024-06-23 01:00:00','2024-06-23 01:00:00'),
(4,'赵六','2024-06-23 01:00:00','2024-06-23 01:00:00');