-----------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分表结构图

image.png

020

020

------1.6查询数据

SELECT * FROM split_table_mouth_test;

image.png

020

SELECT * FROM split_table_mouth_test_01;

image.png

020

SELECT * FROM split_table_mouth_test_02;

image.png

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');

image.png

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分表结构图

image.png

020

020

------2.6查询数据

-------------查询表单

SELECT * FROM split_table_mouth_test1;

image.png

020

SELECT * FROM split_table_mouth_test1_01;

image.png

020

SELECT * FROM split_table_mouth_test1_02;

image.png

020

SELECT * FROM split_table_mouth_test1_03;

image.png

020

SELECT * FROM split_table_mouth_test1_12;

020

image.png

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);

image.png

020

------4.分表结构图

020

image.png

020

020

020

------5.查询表单

SELECT * FROM split_table_list_test;

image.png

020

SELECT * FROM split_table_list_test_1;

image.png

020

SELECT * FROM split_table_list_test_2;

image.png

020

SELECT * FROM split_table_list_test_3;

image.png

020

SELECT * FROM split_table_list_test_4;

image.png

020

SELECT * FROM split_table_list_test_5;

image.png

020