postgres分区表的创建-基于继承-Toy模板网

这篇具有很好参考价值的文章主要介绍了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 $$
BEGIN
      IF (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.MEASUREMENT
    FOR EACH ROW EXECUTE FUNCTION APPS.MEASUREMENT_INSERT_TRIGGER();
	
CREATE or replace  TRIGGER INSERT_MEASUREMENT_TRIGGER
    BEFORE INSERT ON APPS.MEASUREMENT
    FOR 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=# \d
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 apps   | measurement          | table | postgres
 apps   | measurement_y2023m01 | table | postgres
 apps   | measurement_y2023m02 | table | postgres
 apps   | measurement_y2023m03 | table | postgres
 apps   | measurement_y2023m04 | table | postgres
 apps   | measurement_y2023m05 | table | postgres
 apps   | persons              | table | postgres
 apps   | students             | table | postgres
 apps   | t1                   | table | postgres
(9 rows)

test=# 


test=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       1 | 2023-01-02 |        2 |         2
       2 | 2023-02-02 |        2 |         2
       3 | 2023-03-02 |        2 |         2
       4 | 2023-04-02 |        2 |         2
       5 | 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 $$
BEGIN
      IF (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 relations
 Schema |         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文章来源地址https://www.toymoban.com/news/detail-406903.html

到了这里，关于postgres分区表的创建-基于继承的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！