pg分区表一

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 zoneDEFAULT now(),  
cellidfrom character varying(200),  
address character varying(200),  
mark integerDEFAULT 100,  
fail_times integerDEFAULT 0,  
success_times integerDEFAULT 0,  
skyid integerDEFAULT 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();

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)