当前位置： 移动技术网 > IT编程>开发语言>.net > postgreSql DDL 分区表

postgreSql DDL 分区表

2020年07月07日  | 移动技术网IT编程  | 我要评论
  • 建表(删表)
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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网