建设淘宝网站的市场分析,wordpress第三方jquery,抖音小程序代理,免费行情网站下载大全参考文档#xff1a; http://postgres.cn/docs/12/ddl-partitioning.html
创建基于继承的分区表的步骤 1 创建父表 2 创建子表#xff0c;从父表继承过来 3 创建函数及触发器#xff0c;使插入的数据根据规则#xff0c;插入到对应的子表中
-- 创建父表
CREATE TABLE a…参考文档 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
