当前位置： 首页 > news >正文

postgres分区表的创建-基于继承

news 2025/9/17 17:24:41

参考文档：
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

http://www.lryc.cn/news/58276.html

相关文章：

  • Docker应用部署
  • 使用golang实现日志收集系统的logagent
  • 小红书点赞不显示怎么回事?小红书笔记评论被吞怎么办
  • 地址变换和缺页置换习题
  • PAT 乙级 1010 一元多项式求导（解题思路+AC代码）
  • 一维河流污染持续排放模拟（水污染扩散）
  • 数据优化 | CnOpenDataA股上市公司招聘数据
  • nacos和eureka的区别
  • canvas.toDataURL生成图片报错的解决方案
  • 电容笔和Apple pencil的区别是什么？好用电容笔推荐
  • 关于onnx 转ncnn 的问题
  • 设计模式之《责任链模式》
  • Android Studio实现多功能日记本
  • 只依赖Tensorrt和opencv的yolov5源代码
  • 多路I/O转接 poll（了解）
  • 听说你也在为配置tomcat server而烦恼，看我这一篇，让你醍醐灌顶！
  • 【从零开始学Skynet】工具篇（二）：虚拟机文件的复制粘贴
  • 全球自动驾驶竞争力最新排行榜，4家中国企业上榜
  • APP启动流程分析
  • IIR数字滤波器简介与实现
  • 3.5 函数的极值与最大值和最小值
  • 第五十八天打卡
  • 双一流大学计算机专业月薪拿2000？网友：我裂开
  • ChatGPT的“N宗罪”？｜AI百态（上篇）
  • 48.现有移动端开源框架及其特点—MDL（mobile-deep-learning）
  • 4.9--计算机网络之TCP篇之TCP Keepalive 和 HTTP Keep-Alive --（复习＋大总结）---沉淀ing
  • qt完善登录界面（2023-4-6）
  • 104.（cesium篇）cesium卫星轨道模拟
  • Linux shell编程
  • Rasa 3.x 学习系列-Rasa [3.5.4] -2023-04-05新版本发布