-----------RANGE分表方式
*1.按年月分表 *
------1.1创建表单
CREATE TABLE split_table_mouth_test(
id INT8 not null,
create_by VARCHAR(50) NOT NULL,
create_date TIMESTAMP(0) NOT NULL
) partition by range(create_date);
------1.2指定id排序
CREATE SEQUENCE split_table_mouth_test_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER TABLE "payroll_analysis"."split_table_mouth_test" ALTER COLUMN id SET DEFAULT nextval('split_table_mouth_test_id_seq');
------1.3.创建分表
CREATE TABLE split_table_mouth_test_01 PARTITION OF split_table_mouth_test FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE split_table_mouth_test_02 PARTITION OF split_table_mouth_test FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
.....
------1.4插入数据
INSERT INTO split_table_mouth_test (create_by, create_date)VALUES ('Event 1', '2023-01-15 00:00:00');
INSERT INTO split_table_mouth_test (create_by, create_date)VALUES ('Event 1', '2023-02-15 00:00:00');
------不创建对应分表 insert报错
INSERT INTO split_table_mouth_test (create_by, create_date)VALUES ('Event 1', '2023-03-15 00:00:00');
------1.5分表结构图
020
020
------1.6查询数据
SELECT * FROM split_table_mouth_test;
020
SELECT * FROM split_table_mouth_test_01;
020
SELECT * FROM split_table_mouth_test_02;
020
*2.按月分表 *
------2.1创建表单
CREATE TABLE split_table_mouth_test1 (
id INT8 not null,
create_by VARCHAR(50) NOT NULL,
create_date TIMESTAMP(0) NOT NULL
) PARTITION BY RANGE (EXTRACT(MONTH FROM create_date));
------2.2指定id排序
CREATE SEQUENCE split_table_mouth_test1_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER TABLE "payroll_analysis"."split_table_mouth_test1" ALTER COLUMN id SET DEFAULT nextval('split_table_mouth_test1_id_seq');
------2.3创建分表
CREATE TABLE split_table_mouth_test1_01 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (1) TO (2);
CREATE TABLE split_table_mouth_test1_02 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (2) TO (3);
CREATE TABLE split_table_mouth_test1_03 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (3) TO (4);
CREATE TABLE split_table_mouth_test1_04 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (4) TO (5);
CREATE TABLE split_table_mouth_test1_05 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (5) TO (6);
CREATE TABLE split_table_mouth_test1_06 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (6) TO (7);
CREATE TABLE split_table_mouth_test1_07 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (7) TO (8);
CREATE TABLE split_table_mouth_test1_08 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (8) TO (9);
CREATE TABLE split_table_mouth_test1_09 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (9) TO (10);
CREATE TABLE split_table_mouth_test1_10 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (10) TO (11);
CREATE TABLE split_table_mouth_test1_11 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (11) TO (12);
CREATE TABLE split_table_mouth_test1_12 PARTITION OF split_table_mouth_test1 FOR VALUES FROM (12) TO (13);
------2.4插入数据
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2023-01-15 00:00:00');
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2022-01-15 00:00:00');
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2021-01-15 00:00:00');
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2023-02-15 00:00:00');
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2022-02-15 00:00:00');
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2021-02-15 00:00:00');
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2023-03-15 00:00:00');
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2022-03-15 00:00:00');
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2021-03-15 00:00:00');
------不创建对应分表 insert报错
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2021-04-15 00:00:00');
020
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2021-12-15 00:00:00');
INSERT INTO split_table_mouth_test1 (create_by, create_date)VALUES ('Event 1', '2021-12-31 00:00:00');
------2.5分表结构图
020
020
------2.6查询数据
-------------查询表单
SELECT * FROM split_table_mouth_test1;
020
SELECT * FROM split_table_mouth_test1_01;
020
SELECT * FROM split_table_mouth_test1_02;
020
SELECT * FROM split_table_mouth_test1_03;
020
SELECT * FROM split_table_mouth_test1_12;
020
020
-----------HASH分表方式
020
020
------------------按id分表 取模 创建表单报错 错误信息: unrecognized partitioning strategy "hash"
CREATE TABLE split_table_id_test(
id INT8 not null,
create_by VARCHAR(50) NOT NULL,
create_date TIMESTAMP(0) NOT NULL
) partition by hash(id);
020
020
-----------list分表方式
020
------1.创建表单
020
CREATE TABLE split_table_list_test ( a int ) PARTITION BY LIST (a);
------2.创建分表
CREATE TABLE split_table_list_test_1 PARTITION OF split_table_list_test FOR VALUES IN (1);
CREATE TABLE split_table_list_test_2 PARTITION OF split_table_list_test FOR VALUES IN (2);
CREATE TABLE split_table_list_test_3 PARTITION OF split_table_list_test FOR VALUES IN (3);
CREATE TABLE split_table_list_test_4 PARTITION OF split_table_list_test FOR VALUES IN (4);
CREATE TABLE split_table_list_test_5 PARTITION OF split_table_list_test FOR VALUES IN (5);
------3.插入数据
INSERT INTO split_table_list_test ("a")VALUES (1);
INSERT INTO split_table_list_test ("a")VALUES (2);
INSERT INTO split_table_list_test ("a")VALUES (3);
INSERT INTO split_table_list_test ("a")VALUES (4);
INSERT INTO split_table_list_test ("a")VALUES (5);
------不创建对应分表 insert报错
INSERT INTO split_table_list_test ("a")VALUES (6);
020
------4.分表结构图
020
020
020
020
------5.查询表单
SELECT * FROM split_table_list_test;
020
SELECT * FROM split_table_list_test_1;
020
SELECT * FROM split_table_list_test_2;
020
SELECT * FROM split_table_list_test_3;
020
SELECT * FROM split_table_list_test_4;
020
SELECT * FROM split_table_list_test_5;
020
