欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

postgreSql DDL 分区表

程序员文章站 2022-03-30 10:17:04
DROP TABLE IF EXISTS peimc_statistic_video_meeting_agg_company;CREATE TABLE peimc_statistic_video_meeting_agg_company ( "statisticId" varchar(32) COLLATE "default", "statisticDate" varchar(10) CO......
  • 建表(删表)
DROP TABLE IF EXISTS statistic_meeting ;
CREATE TABLE statistic_meeting (
  "statistic_id" varchar(32) COLLATE "default",
  "statistic_date" varchar(8) COLLATE "default",
  "company_id" varchar(16) COLLATE "default",
  "statistic_type" int2 NOT NULL,
  "create_count" int4 DEFAULT 0,
  "create_success_count" int4 DEFAULT 0,
  "connect_success_count" int4 DEFAULT 0,
  "invite_count" int4 DEFAULT 0,
  "answer_success_count" int4 DEFAULT 0,
  "video_call_total_time" int8 DEFAULT 0,
  "total_member_attend_time" int8 DEFAULT 0,
  "created_time" TIMESTAMP (6) NOT NULL,
  PRIMARY KEY ("statisticId")
)
WITH (OIDS=FALSE);

COMMENT ON COLUMN statistic_meeting."statistic_id" IS 'statistic_date + company_id + statistic_type';
COMMENT ON COLUMN statistic_meeting."statistic_date " IS 'YYYYMMDD 统计日期';
COMMENT ON COLUMN statistic_meeting."statistic_type" IS '类型，0-日数据 1-周数据 2-月数据 4-当月数据';
COMMENT ON COLUMN statistic_meeting."company_id" IS '子机构ID';
COMMENT ON COLUMN statistic_meeting."create_count" IS '总发起数';
COMMENT ON COLUMN statistic_meeting."create_success_count" IS '成功创建视频数';
COMMENT ON COLUMN statistic_meeting."connect_success_count" IS '成功接通视频数';
COMMENT ON COLUMN statistic_meeting."invite_count" IS '邀请总数';
COMMENT ON COLUMN statistic_meeting."answer_success_count" IS '接听成功数';
COMMENT ON COLUMN statistic_meeting."video_call_total_time" IS '视频通话总时长';
COMMENT ON COLUMN statistic_meeting."total_member_attend_time" IS '参会人次通话总时长';

CREATE INDEX index_statistic_meeting ON statistic_meeting USING btree (statistic_date, company_id, statistic_type);

GRANT select, insert, update, delete on statistic_meeting to 数据库用户名;
  • 新增删除字段
ALTER TABLE statistic_meeting ADD "create_success_rate" numeric(10,3) DEFAULT 0;

ALTER TABLE statistic_meeting DROP COLUMN IF EXISTS "create_success_rate";
  • 分区表
-- 先创建一张主表
-- 再创建分区表
-- mybatis使用主表名
CREATE TABLE "step_detail" (
  "id" VARCHAR (64) COLLATE "default" NOT NULL,
  "operation_time" TIMESTAMP (6) DEFAULT NOW(),
  "created_time" TIMESTAMP (6) NOT NULL,
  "updated_time" TIMESTAMP (6) NOT NULL,
  "platform" int2,
  "sdk" VARCHAR (10) COLLATE "default",
  "version" VARCHAR (10) COLLATE "default",
  "apn" VARCHAR (20) COLLATE "default",
  "short_link" bool,
  "channel" VARCHAR (10) COLLATE "default",
  "remote_ip" VARCHAR (16) COLLATE "default",
  "server_ip" VARCHAR (16) COLLATE "default",
  "retries_count" int4,
  "io_log" VARCHAR (2000) COLLATE "default",
  CONSTRAINT "step_detail_pkey_00" PRIMARY KEY ("id")
) WITH (OIDS = FALSE);
CREATE INDEX "idx_step_detail_time_00" ON "step_detail" USING btree ("operation_time");

-- 第一个月数据存放分区表
DROP TABLE IF EXISTS step_detail_202001;
CREATE TABLE "step_detail_202001" (
  "id" VARCHAR (64) COLLATE "default" NOT NULL,
  "operation_time" TIMESTAMP (6) DEFAULT NOW(),
  "created_time" TIMESTAMP (6) NOT NULL,
  "updated_time" TIMESTAMP (6) NOT NULL,
  "platform" int2,
  "sdk" VARCHAR (10) COLLATE "default",
  "version" VARCHAR (10) COLLATE "default",
  "apn" VARCHAR (20) COLLATE "default",
  "short_link" bool,
  "channel" VARCHAR (10) COLLATE "default",
  "remote_ip" VARCHAR (16) COLLATE "default",
  "server_ip" VARCHAR (16) COLLATE "default",
  "retries_count" int4,
  "io_log" VARCHAR (2000) COLLATE "default",
  CONSTRAINT "step_detail_pkey_202001" PRIMARY KEY ("id"),
  CONSTRAINT "step_detail_202001" CHECK (((created_time >= '2020-01-01'::date) AND (created_time < '2020-02-01'::date)))
)
  INHERITS ("step_detail")
WITH (OIDS=FALSE)
;
CREATE INDEX idx_step_detail_time_202001 ON step_detail_202001 USING btree (operation_time);

-- 第二个月数据存放分区表
DROP TABLE IF EXISTS step_detail_202002;
CREATE TABLE "step_detail_202002" (
  "id" VARCHAR (64) COLLATE "default" NOT NULL,
  "operation_time" TIMESTAMP (6) DEFAULT NOW(),
  "created_time" TIMESTAMP (6) NOT NULL,
  "updated_time" TIMESTAMP (6) NOT NULL,
  "platform" int2,
  "sdk" VARCHAR (10) COLLATE "default",
  "version" VARCHAR (10) COLLATE "default",
  "apn" VARCHAR (20) COLLATE "default",
  "short_link" bool,
  "channel" VARCHAR (10) COLLATE "default",
  "remote_ip" VARCHAR (16) COLLATE "default",
  "server_ip" VARCHAR (16) COLLATE "default",
  "retries_count" int4,
  "io_log" VARCHAR (2000) COLLATE "default",
  CONSTRAINT "step_detail_pkey_202002" PRIMARY KEY ("id"),
  CONSTRAINT "step_detail_202002" CHECK (((created_time >= '2020-02-01'::date) AND (created_time < '2020-03-01'::date)))
)
  INHERITS ("step_detail")
WITH (OIDS=FALSE)
;
CREATE INDEX idx_step_detail_time_202002 ON step_detail_202002 USING btree (operation_time);

-- 异常数据存放分区表
DROP TABLE IF EXISTS step_detail_other;
CREATE TABLE "step_detail_other" (
  "id" VARCHAR (64) COLLATE "default" NOT NULL,
  "operation_time" TIMESTAMP (6) DEFAULT NOW(),
  "created_time" TIMESTAMP (6) NOT NULL,
  "updated_time" TIMESTAMP (6) NOT NULL,
  "platform" int2,
  "sdk" VARCHAR (10) COLLATE "default",
  "version" VARCHAR (10) COLLATE "default",
  "apn" VARCHAR (20) COLLATE "default",
  "short_link" bool,
  "channel" VARCHAR (10) COLLATE "default",
  "remote_ip" VARCHAR (16) COLLATE "default",
  "server_ip" VARCHAR (16) COLLATE "default",
  "retries_count" int4,
  "io_log" VARCHAR (2000) COLLATE "default",
  CONSTRAINT "step_detail_pkey_other" PRIMARY KEY ("id"),
  CONSTRAINT "step_detail_other" CHECK (((created_time >= '2020-01-01'::date) AND (created_time < '2099-01-01'::date)))
)
  INHERITS ("step_detail")
WITH (OIDS=FALSE)
;
CREATE INDEX idx_step_detail_time_other ON step_detail_other USING btree (operation_time);

-- 作为主表的分区表
create table hm_agg_step_detail_202001 () inherits (step_detail);
create table hm_agg_step_detail_202002 () inherits (step_detail);
create table hm_agg_step_detail_other () inherits (step_detail);

ALTER TABLE step_detail_202001 OWNER TO 数据库用户名;
ALTER TABLE step_detail_202002 OWNER TO 数据库用户名;
ALTER TABLE step_detail_other OWNER TO 数据库用户名;



-- 删除旧的触发器,函数;
DROP TRIGGER IF EXISTS tgr_step_detail_insert ON step_detail;
DROP FUNCTION IF EXISTS fnc_step_detail_insert();

-- 创建函数
CREATE
OR REPLACE FUNCTION fnc_step_detail_insert () RETURNS TRIGGER AS $$
BEGIN

  IF (
    NEW .created_time >= DATE '2020-01-01'
    AND NEW .created_time < DATE '2020-02-01'
  ) THEN
    INSERT INTO step_detail_202001
    VALUES
      (NEW .*) ;
  ELSIF (
    NEW .created_time >= DATE '2020-02-01'
    AND NEW .created_time < DATE '2020-03-01'
  ) THEN
    INSERT INTO step_detail_202002
    VALUES
      (NEW .*) ;
  ELSE
    INSERT INTO step_detail_other
    VALUES
      (NEW .*) ;
  END
  IF ; RETURN NULL ;
END ; $$ LANGUAGE plpgsql;



DROP TRIGGER IF EXISTS tgr_step_detail_insert ON step_detail;
-- 使用新的函数创建新的触发器
CREATE TRIGGER tgr_step_detail_insert BEFORE INSERT ON step_detail FOR EACH ROW EXECUTE PROCEDURE fnc_step_detail_insert();



GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE step_detail_202001 TO 数据库用户名;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE step_detail_202002 TO 数据库用户名;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE step_detail_other TO 数据库用户名;

本文地址：https://blog.csdn.net/zxczb/article/details/81901175

上一篇： Shell的基础脚本练习合集

下一篇： REDIS集群 基础知识总结

推荐阅读