postgres分区表的创建-基于继承
参考文档：
http://postgres.cn/docs/12/ddl-partitioning.html
创建基于继承的分区表的步骤
1 创建父表
2 创建子表，从父表继承过来
3 创建函数及触发器，使插入的数据根据规则，插入到对应的子表中
-- 创建父表
CREATE TABLE apps.measurement (city_id int not null,logdate date not null,peaktemp int,unitsales int
);test=# CREATE TABLE apps.measurement (
test(# city_id int not null,
test(# logdate date not null,
test(# peaktemp int,
test(# unitsales int
test(# );
CREATE TABLE
test=# -- 创建分区表，5个分区
CREATE TABLE apps.measurement_y2023m01 (CHECK ( logdate >= DATE '2023-01-01' AND logdate < DATE '2023-02-01' )) INHERITS (measurement);
CREATE TABLE apps.measurement_y2023m02 (CHECK ( logdate >= DATE '2023-02-01' AND logdate < DATE '2023-03-01' )) INHERITS (measurement);
CREATE TABLE apps.measurement_y2023m03 (CHECK ( logdate >= DATE '2023-03-01' AND logdate < DATE '2023-04-01' )) INHERITS (measurement);
CREATE TABLE apps.measurement_y2023m04 (CHECK ( logdate >= DATE '2023-04-01' AND logdate < DATE '2023-05-01' )) INHERITS (measurement);
CREATE TABLE apps.measurement_y2023m05 (CHECK ( logdate >= DATE '2023-05-01' AND logdate < DATE '2023-06-01' )) INHERITS (measurement);test=# CREATE TABLE apps.measurement_y2023m01 (CHECK ( logdate >= DATE '2023-01-01' AND logdate < DATE '2023-02-01' )) INHERITS (measurement);
CREATE TABLE
test=# CREATE TABLE apps.measurement_y2023m02 (CHECK ( logdate >= DATE '2023-02-01' AND logdate < DATE '2023-03-01' )) INHERITS (measurement);
CREATE TABLE
test=# CREATE TABLE apps.measurement_y2023m03 (CHECK ( logdate >= DATE '2023-03-01' AND logdate < DATE '2023-04-01' )) INHERITS (measurement);
CREATE TABLE
test=# CREATE TABLE apps.measurement_y2023m04 (CHECK ( logdate >= DATE '2023-04-01' AND logdate < DATE '2023-05-01' )) INHERITS (measurement);
CREATE TABLE
test=# CREATE TABLE apps.measurement_y2023m05 (CHECK ( logdate >= DATE '2023-05-01' AND logdate < DATE '2023-06-01' )) INHERITS (measurement);
CREATE TABLE
test=# -- 创建触发器函数，针对不同月份的数据，落入不同的子表，从而达到数据分区的效果
CREATE OR REPLACE FUNCTION apps.measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGINIF (NEW.logdate >= DATE '2023-01-01' AND NEW.logdate < DATE '2023-02-01') THEN INSERT INTO apps.measurement_y2023m01 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-02-01' AND NEW.logdate < DATE '2023-03-01') THEN INSERT INTO apps.measurement_y2023m02 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-03-01' AND NEW.logdate < DATE '2023-04-01') THEN INSERT INTO apps.measurement_y2023m03 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-04-01' AND NEW.logdate < DATE '2023-05-01') THEN INSERT INTO apps.measurement_y2023m04 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-05-01' AND NEW.logdate < DATE '2023-06-01') THEN INSERT INTO apps.measurement_y2023m05 values (NEW.*); ELSE RAISE EXCEPTION 'Date out of range .Fix the apps.measurement_insert_trigger() function!';END IF;RETURN NULL;
END;
$$
LANGUAGE plpgsql;test=# CREATE OR REPLACE FUNCTION apps.measurement_insert_trigger()
test-# RETURNS TRIGGER AS $$
test$# BEGIN
test$# IF (NEW.logdate >= DATE '2023-01-01' AND NEW.logdate < DATE '2023-02-01') THEN
test$# INSERT INTO apps.measurement_y2023m01 values (NEW.*);
test$#
test$# ELSE IF (NEW.logdate >= DATE '2023-02-01' AND NEW.logdate < DATE '2023-03-01') THEN
test$# INSERT INTO apps.measurement_y2023m02 values (NEW.*);
test$#
test$# ELSE IF (NEW.logdate >= DATE '2023-03-01' AND NEW.logdate < DATE '2023-04-01') THEN
test$# INSERT INTO apps.measurement_y2023m03 values (NEW.*);
test$#
test$# ELSE IF (NEW.logdate >= DATE '2023-04-01' AND NEW.logdate < DATE '2023-05-01') THEN
test$# INSERT INTO apps.measurement_y2023m04 values (NEW.*);
test$#
test$# ELSE IF (NEW.logdate >= DATE '2023-05-01' AND NEW.logdate < DATE '2023-06-01') THEN
test$# INSERT INTO apps.measurement_y2023m05 values (NEW.*);
test$#
test$# ELSE
test$# RAISE EXCEPTION 'Date out of range .Fix the apps.measurement_insert_trigger() function! ';
test$# END IF;
test$# RETURN NULL;
test$# END;
test$# $$
test-# LANGUAGE plpgsql;
CREATE FUNCTION
test=# -- 创建触发器 ，不带schema，创建的触发器，默认就在apps的schema中
CREATE TRIGGER APPS.INSERT_MEASUREMENT_TRIGGER <<<<<< 奇怪，触发器带schema，创建的时候报错 ，BEFORE INSERT ON APPS.MEASUREMENTFOR EACH ROW EXECUTE FUNCTION APPS.MEASUREMENT_INSERT_TRIGGER();CREATE or replace TRIGGER INSERT_MEASUREMENT_TRIGGERBEFORE INSERT ON APPS.MEASUREMENTFOR EACH ROW EXECUTE FUNCTION APPS.MEASUREMENT_INSERT_TRIGGER(); test=# CREATE TRIGGER APPS.INSERT_MEASUREMENT_TRIGGER
test-# BEFORE INSERT ON APPS.MEASUREMENT
test-# FOR EACH ROW EXECUTE FUNCTION APPS.MEASUREMENT_INSERT_TRIGGER();
ERROR: syntax error at or near "."
LINE 1: CREATE TRIGGER APPS.INSERT_MEASUREMENT_TRIGGER^
test=# CREATE or replace TRIGGER INSERT_MEASUREMENT_TRIGGER
test-# BEFORE INSERT ON APPS.MEASUREMENT
test-# FOR EACH ROW EXECUTE FUNCTION APPS.MEASUREMENT_INSERT_TRIGGER();
CREATE TRIGGER
test=# -- 插入数据
CREATE TABLE apps.measurement (city_id int not null,logdate date not null,peaktemp int,unitsales int
);insert into apps.measurement values (1,date '2023-01-02',2,2);
insert into apps.measurement values (2,date '2023-02-02',2,2);
insert into apps.measurement values (3,date '2023-03-02',2,2);
insert into apps.measurement values (4,date '2023-04-02',2,2);
insert into apps.measurement values (5,date '2023-05-02',2,2); test=# insert into apps.measurement values (1,date '2023-01-02',2,2);
INSERT 0 0
test=# insert into apps.measurement values (2,date '2023-02-02',2,2);
INSERT 0 0
test=# insert into apps.measurement values (3,date '2023-03-02',2,2);
INSERT 0 0
test=# insert into apps.measurement values (4,date '2023-04-02',2,2);
INSERT 0 0
test=# insert into apps.measurement values (5,date '2023-05-02',2,2);
INSERT 0 0
test=# -- 查看父表和各个分区表的数据 ，可以看到1个父表，5个分区表
test=# \\dList of relationsSchema | Name | Type | Owner
--------+----------------------+-------+----------apps | measurement | table | postgresapps | measurement_y2023m01 | table | postgresapps | measurement_y2023m02 | table | postgresapps | measurement_y2023m03 | table | postgresapps | measurement_y2023m04 | table | postgresapps | measurement_y2023m05 | table | postgresapps | persons | table | postgresapps | students | table | postgresapps | t1 | table | postgres
(9 rows)test=# test=# select * from measurement;city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------1 | 2023-01-02 | 2 | 22 | 2023-02-02 | 2 | 23 | 2023-03-02 | 2 | 24 | 2023-04-02 | 2 | 25 | 2023-05-02 | 2 | 2
(5 rows)test=# test=# select * from measurement_y2023m01;city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------1 | 2023-01-02 | 2 | 2
(1 row)test=# select * from measurement_y2023m02;city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------2 | 2023-02-02 | 2 | 2
(1 row)test=# select * from measurement_y2023m03;city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------3 | 2023-03-02 | 2 | 2
(1 row)test=# select * from measurement_y2023m04;city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------4 | 2023-04-02 | 2 | 2
(1 row)test=# select * from measurement_y2023m05;city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------5 | 2023-05-02 | 2 | 2
(1 row)test=# -- 插入2023年6月份的数据 ，直接报错 ，因为此时没有创建6月份的子表，触发器函数中也没有处理6月份数据的规则
insert into apps.measurement values (1,date '2023-06-02',2,2);
test=# insert into apps.measurement values (1,date '2023-06-02',2,2);
ERROR: Date out of range .Fix the apps.measurement_insert_trigger() function!
CONTEXT: PL/pgSQL function measurement_insert_trigger() line 23 at RAISE
test=#
-- 创建6月份分区表，
修改触发器函数。使6月份的数据可以插入进去
CREATE TABLE apps.measurement_y2023m06 (CHECK ( logdate >= DATE '2023-06-01' AND logdate < DATE '2023-07-01' )) INHERITS (measurement);CREATE OR REPLACE FUNCTION apps.measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGINIF (NEW.logdate >= DATE '2023-01-01' AND NEW.logdate < DATE '2023-02-01') THEN INSERT INTO apps.measurement_y2023m01 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-02-01' AND NEW.logdate < DATE '2023-03-01') THEN INSERT INTO apps.measurement_y2023m02 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-03-01' AND NEW.logdate < DATE '2023-04-01') THEN INSERT INTO apps.measurement_y2023m03 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-04-01' AND NEW.logdate < DATE '2023-05-01') THEN INSERT INTO apps.measurement_y2023m04 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-05-01' AND NEW.logdate < DATE '2023-06-01') THEN INSERT INTO apps.measurement_y2023m05 values (NEW.*); ELSIF (NEW.logdate >= DATE '2023-06-01' AND <<<<<<< 新加入的规则NEW.logdate < DATE '2023-07-01') THEN INSERT INTO apps.measurement_y2023m06 values (NEW.*);ELSE RAISE EXCEPTION 'Date out of range .Fix the apps.measurement_insert_trigger() function!';END IF;RETURN NULL;
END;
$$
LANGUAGE plpgsql;test=# insert into apps.measurement values (1,date '2023-06-02',2,2); <<<<<< 插入6月份数据
INSERT 0 0
test=# select * from measurement_y2023m06; <<<<<< 查询分区数据 city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------1 | 2023-06-02 | 2 | 2
(1 row)test=#
-- 通过视图查看分区表，查询不到基于继承建立的分区表
test=# select * from pg_partitioned_table ;partrelid | partstrat | partnatts | partdefid | partattrs | partclass | partcollation | partexprs
-----------+-----------+-----------+-----------+-----------+-----------+---------------+-----------
(0 rows)test=# --通过dt+ 命令查看建立的父表和子表
test=# \\dt+ apps.measurement*List of relationsSchema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------------------+-------+----------+-------------+---------------+------------+-------------apps | measurement | table | postgres | permanent | heap | 0 bytes | apps | measurement_y2023m01 | table | postgres | permanent | heap | 8192 bytes | apps | measurement_y2023m02 | table | postgres | permanent | heap | 8192 bytes | apps | measurement_y2023m03 | table | postgres | permanent | heap | 8192 bytes | apps | measurement_y2023m04 | table | postgres | permanent | heap | 8192 bytes | apps | measurement_y2023m05 | table | postgres | permanent | heap | 8192 bytes | apps | measurement_y2023m06 | table | postgres | permanent | heap | 8192 bytes |
(7 rows)test=# END
