postgre 创建分区表

喜欢ヅ旅行 2023-02-17 13:26 21阅读 0赞

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();