pg分区表一

2023-12-14 19:23:21

PostgreSQL的分区表创建较Oracle分区表复杂些，需要借助创建继承表和触发器的模式实现, 具体创建方法可以参照以下实验, 供参考。

创建父表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE fenqu_table (  
 id integer NOT NULL,  
 mcc character varying(5),  
 lac character varying(8),  
 mnc character(2),  
 cellid character varying(8),  
 signal integer,  
 antennaheight integer,  
 cellpower integer,  
 addtime timestamp(0) without time zone DEFAULT now(),  
 cellidfrom character varying(200),  
 address character varying(200),  
 mark integer DEFAULT 100,  
 fail_times integer DEFAULT 0,  
 success_times integer DEFAULT 0,  
 skyid integer DEFAULT 0,  
 mapaddress character varying(200)  
);

创建子表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
CREATE TABLE fenqu_table_p200905 (  
 id integer,  
 mcc character varying(5),  
 lac character varying(8),  
 mnc character(2),  
 cellid character varying(8),  
 signal integer,  
 antennaheight integer,  
 cellpower integer,  
 addtime timestamp(0) without time zone,  
 cellidfrom character varying(200),  
 address character varying(200),  
 mark integer,  
 fail_times integer,  
 success_times integer,  
 skyid integer,  
 mapaddress character varying(200),  
 CONSTRAINT fenqu_table_p200905_addtime_check CHECK (((addtime >= '2009-05-01 00:00:00'::timestamp without time zone) AND (addtime < '2009-06-01 00:00:00'::timestamp without time zone)))  
)  
INHERITS (fenqu_table);

CREATE TABLE fenqu_table_p200906 (  
 id integer,  
 mcc character varying(5),  
 lac character varying(8),  
 mnc character(2),  
 cellid character varying(8),  
 signal integer,  
 antennaheight integer,  
 cellpower integer,  
 addtime timestamp(0) without time zone,  
 cellidfrom character varying(200),  
 address character varying(200),  
 mark integer,  
 fail_times integer,  
 success_times integer,  
 skyid integer,  
 mapaddress character varying(200),  
 CONSTRAINT fenqu_table_p200906_addtime_check CHECK (((addtime >= '2009-06-01 00:00:00'::timestamp without time zone) AND (addtime < '2009-07-01 00:00:00'::timestamp without time zone)))  
)  
INHERITS (fenqu_table);

CREATE TABLE fenqu_table_p200907 (  
 id integer,  
 mcc character varying(5),  
 lac character varying(8),  
 mnc character(2),  
 cellid character varying(8),  
 signal integer,  
 antennaheight integer,  
 cellpower integer,  
 addtime timestamp(0) without time zone,  
 cellidfrom character varying(200),  
 address character varying(200),  
 mark integer,  
 fail_times integer,  
 success_times integer,  
 skyid integer,  
 mapaddress character varying(200),  
 CONSTRAINT fenqu_table_p200907_addtime_check CHECK (((addtime >= '2009-07-01 00:00:00'::timestamp without time zone) AND (addtime < '2009-08-01 00:00:00'::timestamp without time zone)))  
)  
INHERITS (fenqu_table);

创建数据删除触发器函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE OR REPLACE FUNCTION public.fenqu_table_delete_trigger()  
RETURNS trigger  
LANGUAGE plpgsql  
AS $function$  
BEGIN  
 IF ( OLD.addtime>='2009-05-01' and OLD.addtime<'2009-06-01' ) THEN  
 DELETE FROM fenqu_table_p200905 where cretime=OLD.cretime;  
 ELSIF ( OLD.addtime>='2009-06-01' and OLD.addtime<'2009-07-01' ) THEN  
 DELETE FROM fenqu_table_p200906 where cretime=OLD.cretime;  
 ELSIF ( OLD.addtime>='2009-07-01' and OLD.addtime<'2009-08-01' ) THEN  
 DELETE FROM fenqu_table_p200907 where cretime=OLD.cretime;  
 ELSE  
 RAISE EXCEPTION 'addtime out of range. Fix the fenqu_table_delete_trigger() function!';  
 END IF;  
 RETURN NULL;  
END;  
$function$

创建数据插入触发器函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE OR REPLACE FUNCTION public.fenqu_table_insert_trigger()  
RETURNS trigger  
LANGUAGE plpgsql  
AS $function$  
BEGIN  
 IF ( NEW.addtime>='2009-05-01' and NEW.addtime<'2009-06-01' ) THEN  
 INSERT INTO fenqu_table_p200905 VALUES (NEW.*);  
 ELSIF ( NEW.addtime>='2009-06-01' and NEW.addtime<'2009-07-01' ) THEN  
 INSERT INTO fenqu_table_p200906 VALUES (NEW.*);  
 ELSIF ( NEW.addtime>='2009-07-01' and NEW.addtime<'2009-08-01' ) THEN  
 INSERT INTO fenqu_table_p200907 VALUES (NEW.*);  
 ELSE  
 RAISE EXCEPTION 'addtime out of range. Fix the fenqu_table_insert_trigger() function!';  
 END IF;  
 RETURN NULL;  
END;  
$function$

创建数据插入、删除触发器

1
2
 
create trigger delete_fenqu_table_trigger BEFORE DELETE ON fenqu_table FOR EACH ROW EXECUTE PROCEDURE fenqu_table_delete_trigger();  
create trigger insert_fenqu_table_trigger BEFORE INSERT ON fenqu_table FOR EACH ROW EXECUTE PROCEDURE fenqu_table_insert_trigger();

create sequence

1
2
3
4
 
create sequence fenqu_table_id_seq  
INCREMENT by 1  
MINVALUE 1  
start with 1;

 

分区表测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
mydb=> insert into fenqu_table (id,addtime) values (nextval('fenqu_table_id_seq'),'2009-05-01 00:00:00');  
INSERT 0 0

mydb=> select count(*) from fenqu_table;  
count  
-------  
 1  
(1 row)

mydb=> select count(*) from only fenqu_table;  
count  
-------  
 0  
(1 row)

mydb=> select count(*) from fenqu_table_p200905;  
count  
-------  
 1  
(1 row)
上一篇：pg的json类型


下一篇：PG-监控