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

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

news 2026/2/7 16:32:34

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

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

参考文档： http://postgres.cn/docs/12/ddl-partitioning.html 创建基于继承的分区表的步骤 1 创建父表 2 创建子表，从父表继承过来 3 创建函数及触发器，使插入的数据根据规则，插入到对应的子表中 -- 创建父表 CREATE TABLE a…...

编程日记 2023/4/13 18:29:09

Docker应用部署

文章目录Docker 应用部署一、部署MySQL二、部署Tomcat三、部署Nginx四、部署RedisDocker 应用部署一、部署MySQL 搜索mysql镜像 docker search mysql拉取mysql镜像 docker pull mysql:5.6创建容器，设置端口映射、目录映射 # 在/root目录下创建mysql目录用于存…...

编程日记 2023/4/13 18:24:08

使用golang实现日志收集系统的logagent

整体架构参考七米老师的日志收集项目主要用go实现logagent的部分，logagent的作用主要是实时监控日志追加的变化，并将变化发送到kafka中。之前我们已经实现了用go连接kafka并向其中发送数据，也实现了使用tail库监控日志追加操作。我们…...

编程日记 2023/5/31 13:05:20

小红书点赞不显示怎么回事?小红书笔记评论被吞怎么办

小红书作为一个互联网产品，是一个软件。既然是软件就会有一定的程序漏洞，这是无法避免的。但是很多时候其实并不一定是漏洞的问题。今天就来和大家谈谈小红书点赞不显示怎么回事，小红书评论被吞又是怎么一回事，这些难道都是程序性…...

编程日记 2023/4/13 18:14:05

地址变换和缺页置换习题

1.设某进程页面的访问序列为4,3,2,1,4,3,5,4,3，2,1,5，当分配给该进程的内存页框数分别为3和4时，对于先进先出，最近最少使用，最佳页面置换算法，分别发生多少次缺页中断？ 答： 分配的…...

编程日记 2023/4/13 18:09:04

PAT 乙级 1010 一元多项式求导（解题思路+AC代码）

题目： 设计函数求一元多项式的导数。（注：xn（n为整数）的一阶导数为nxn−1。） 输入格式: 以指数递降方式输入多项式非零项系数和指数（绝对值均为不超过 1000 的整数）。数字间以空格分…...

编程日记 2023/4/13 18:04:03

一维河流污染持续排放模拟（水污染扩散）

一、处理河道转换为geojson数据以淮河为例处理示例数据： {"type": "FeatureCollection","features": [{"geometry": {"coordinates": [[[115.5803,34.4982],[115.5922,34.498],[115.6061,34.4994],[115.6203,…...

编程日记 2023/4/13 17:59:02

数据优化 | CnOpenDataA股上市公司招聘数据

就业是经济的“晴雨表”，更是社会的“稳定器”。稳定和扩大就业一直是国家宏观调控的重要目标，2021年中央经济工作会议八次提到“就业”这一关键词。在新冠肺炎疫情蔓延、世界经济下行及人口老龄化加快等多重因素的叠加之下，稳就业保民生成为…...

编程日记 2023/4/13 17:54:01

nacos和eureka的区别

nacos和eureka的区别 Eureka是什么 Eureka详解Nacos是什么 Nacos详解Nacos和Eureka的区别 CAP理论连接方式服务异常剔除操作实例方式自我保护机制 Eureka是什么 Eureka 是Spring Cloud 微服务框架默认的也是推荐的服务注册中心,由Netflix公司与2012将其开源出来,Eureka基于RE…...

编程日记 2023/5/31 21:50:56

canvas.toDataURL生成图片报错的解决方案

问题原因： toDataURL方法存在跨域限制，如果执行时dom内含有跨域的图片则浏览器执行时会报错。这个根据不同的系统有不同的表现，例如：生成完毕但控制台有warning类型的警告，或者直接异常报error。解决思路&#xff…...

编程日记 2023/4/13 17:43:57

电容笔和Apple pencil的区别是什么？好用电容笔推荐

Apple Pencil与目前市场上常见的电容笔最大的不同之处在于，普通电容笔并不具备苹果Pencil特有的重力压感，而仅仅是一种倾斜的压感。不过，其在其它方面的表现也很出色，与Apple Pencil相似，而且价格仅为200元。现在&…...

编程日记 2023/5/31 14:39:31

文章目录修改模型Detect层设计转换后处理优质文章由于有些操作是没法支持的如5维的操作： Unsupported slice axes ! Unsupported slice axes ! Unsupported slice axes ! Unsupported slice axes ! Unsupported slice axes ! Unsupported slice axes !参考&#…...

编程日记 2023/5/31 13:08:06

设计模式之《责任链模式》

------《责任链模式》责任链模式的概念为什么用责任链模式工作中用在哪里设计思路代码实现总结责任链模式的概念责任链模式是一种行为型设计模式，它允许你将请求沿着处理链传递，直到有一个处理者能够处理该请求为止。在责任链模式中，每个…...

编程日记 2023/4/13 17:28:54

Android Studio实现多功能日记本

项目目录一、项目概述二、系统特点三、开发环境四、详细设计1、E-R图2、数据库3、系统设置五、运行演示一、项目概述本次实现了功能实用且齐全的日记本，界面友好，使用便捷，采用MVC架构设计。使用SQLite数据库存储数据，数据表有主…...

编程日记 2023/4/13 17:23:52

只依赖Tensorrt和opencv的yolov5源代码

simple_yolo.hpp #ifndef SIMPLE_YOLO_HPP #define SIMPLE_YOLO_HPP/*简单的yolo接口，容易集成但是高性能 */#include <vector> #include <memory> #include <string> #include <future> #include <opencv2/opencv.hpp>namespace Si…...

编程日记 2023/5/31 21:52:37

多路I/O转接 poll（了解）

poll() 的机制与 select() 类似，与 select() 在本质上没有多大差别，管理多个描述符也是进行轮询，根据描述符的状态进行处理，但是 poll() 没有最大文件描述符数量的限制（但是数量过大后性能也是会下降）。 p…...

编程日记 2023/5/31 13:10:52

听说你也在为配置tomcat server而烦恼，看我这一篇，让你醍醐灌顶！

一.通过maven创建项目二.下载tomcat服务器我们一般在tomcat官网中进行tomcat的下载 Apache Tomcat - Welcome! 三.添加配置：我们点击下图中的文件配置四.测试配置的tomcat 我们在文件的body中输入测试内容： 在控制台中显式tomcat运行的信息&#…...

编程日记 2023/4/13 17:08:49

【从零开始学Skynet】工具篇（二）：虚拟机文件的复制粘贴

大家在Linux系统下开发的时候肯定会遇到虚拟机与主机间无法复制粘贴的问题，现在我们就来解决这样的问题，方便我们的开发。 1、打开设置我们可以系统界面的菜单栏点击“控制”，然后打开“设置”； 也可以在VirtualBox界面打开“设…...

编程日记 2023/4/13 17:03:48

全球自动驾驶竞争力最新排行榜，4家中国企业上榜

发展至今，自动驾驶技术不仅是汽车行业的一个主战场，更是全球科技领域中备受关注和充满竞争的一个重要领域。近年来，各大汽车制造商和科技公司都在投入大量财力物力人力进行自动驾驶技术的研发，并进一步争夺市场份额。当然&#…...

编程日记 2023/5/31 13:13:11

APP启动流程分析

1、要分析的问题 1、与正常trace比对，确认过耗时在哪个步骤（am create/pause/stop/start/doframe)？ 2、与正常trace比对，确认过耗时在哪个cpu state(Running/Runnable/Sleep/Uninterruptible Sleep)？ 2、启动分析 …...

编程日记 2023/6/1 6:36:47