postgre 创建分区表

PG 创建分区表

1.建表

CREATE TABLE  "exchange_detail" (
  "id" int8 primary key,
  "exchange_type" int2,
  "exchange_text" varchar(255),
  "detail_user" int8,
  "create_time" timestamp(6)
);

COMMENT ON COLUMN "public"."exchange_detail"."id" IS '置换记录id';
COMMENT ON COLUMN "public"."exchange_detail"."exchange_type" IS '置换类型';
COMMENT ON COLUMN "public"."exchange_detail"."exchange_text" IS '置换描述';
COMMENT ON COLUMN "public"."exchange_detail"."detail_user" IS '置换用户';
COMMENT ON COLUMN "public"."exchange_detail"."create_time" IS '创建时间'; 

2.创建分区表 直接继承exchange_detail表就好了。一般的子表不需要加字段。可以建立多个分区

 create table exchange_detail01
(CHECK (extract(month from  create_time) = 1))
INHERITS (exchange_detail);

2.1 给字表增加主键

ALTER TABLE exchange_detail01 ADD PRIMARY KEY (id);

3.给子表创建索引 这个索引一般都是创建规则的字段

create index exchange_detail01_create_time ON exchange_detail01(create_time);

4.创建触发器函数

CREATE OR REPLACE FUNCTION  exchange_detail_trigger() RETURNS  trigger AS $$
BEGIN
 
IF (
extract(month from  NEW .create_time) = 1 
) THEN
INSERT INTO exchange_detail01
VALUES
(NEW .*) ;
ELSEIF (
extract(month from  NEW .create_time) = 2
) THEN
INSERT INTO exchange_detail02
VALUES
(NEW .*) ;
ELSEIF (
extract(month from  NEW .create_time) = 3
) THEN
INSERT INTO exchange_detail03
VALUES
(NEW .*) ;
ELSEIF (
extract(month from  NEW .create_time) = 4
) THEN
INSERT INTO exchange_detail04
VALUES
(NEW .*) ;
ELSEIF(
extract(month from  NEW .create_time) = 5
)THEN
INSERT INTO exchange_detail05
VALUES
(NEW .*) ;
ELSEIF(
extract(month from  NEW .create_time) = 6
)THEN
INSERT INTO exchange_detail06
VALUES
(NEW .*) ;
ELSEIF(
extract(month from  NEW .create_time) = 7
)THEN
INSERT INTO exchange_detail07
VALUES
(NEW .*) ;
ELSEIF(
extract(month from  NEW .create_time) = 8
)THEN
INSERT INTO exchange_detail08
VALUES
(NEW .*) ;
ELSEIF(
extract(month from  NEW .create_time) = 9
)THEN
INSERT INTO exchange_detail09
VALUES
(NEW .*) ;
ELSEIF(
extract(month from  NEW .create_time) = 10
)THEN
INSERT INTO exchange_detail10
VALUES
(NEW .*) ;
ELSEIF(
extract(month from  NEW .create_time) = 11
)THEN
INSERT INTO exchange_detail11
VALUES
(NEW .*) ;
ELSEIF(
extract(month from  NEW .create_time) = 12
)THEN
INSERT INTO exchange_detail12
VALUES
(NEW .*) ;
ELSE
RAISE EXCEPTION 'Date out of range!' ;
END
IF ; RETURN NULL ;
END ; $$ LANGUAGE plpgsql;

5.创建触发器

CREATE TRIGGER exchange_detail_trigger BEFORE INSERT ON exchange_detail
FOR EACH ROW
EXECUTE PROCEDURE exchange_detail_trigger();