postgreSql DDL 分区表

程序员文章站 2022-07-07 20:12:58

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

上一篇：怀孕期间，到底不能吃啥？纠结好久了……

下一篇：男人想排毒，这8种食物是你的得力助手!

postgreSql DDL 分区表

ORACLE 分区表的设计

DDL、DML和DCL的区别与理解

MSSQL监控数据库的DDL操作(创建，修改，删除存储过程，创建，修改，删除表等)

CYQ.Data 支持 PostgreSQL 数据库

SQLServer EVENTDATA()函数来获取DDL 触发器信息

解决MySQL 5.7中定位DDL被阻塞的问题

MySQL数据库与PostgreSQL数据库比较哪个数据库更好些?

通过实例学习MySQL分区表原理及常用操作

Oracle轻松取得建表和索引的DDL语句

MySQL分区表的局限和限制详解

postgreSql DDL 分区表

ORACLE 分区表的设计

DDL、DML和DCL的区别与理解

MSSQL监控数据库的DDL操作(创建，修改，删除存储过程，创建，修改，删除表等)

CYQ.Data 支持 PostgreSQL 数据库

SQLServer EVENTDATA()函数来获取DDL 触发器信息

解决MySQL 5.7中定位DDL被阻塞的问题

MySQL数据库与PostgreSQL数据库比较 哪个数据库更好些?

通过实例学习MySQL分区表原理及常用操作

Oracle轻松取得建表和索引的DDL语句

MySQL分区表的局限和限制详解

MySQL数据库与PostgreSQL数据库比较哪个数据库更好些?