Postgresql建分区表步骤sql

分区表步骤sql"/>

Postgresql建分区表步骤sql

由于业务发展，需要对系统表进行分区处理，以提高查询速度，下面代码是示例，以partition_table_name表为例。

--备份表
SELECT * INTO partition_table_name_2022_bak FROM partition_table_name;
--删除原表
DROP TABLE IF EXISTS "public"."partition_table_name";
--创建带分区的原表
CREATE TABLE "public"."partition_table_name" ("id" int8 NOT NULL DEFAULT '-1'::integer,"tenant_id" int8 NOT NULL DEFAULT '-1'::integer,"app_id" int8 NOT NULL DEFAULT '-1'::integer,"data_id" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,"data_type" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,"data_version" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,"data_format" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,"data_source" varchar(32) COLLATE "pg_catalog"."default","data" text COLLATE "pg_catalog"."default","status" char(1) COLLATE "pg_catalog"."default" NOT NULL,"status_time" timestamp(6),"create_time" timestamp(6) NOT NULL
)
--分区字段 可支持多个
PARTITION BY RANGE ("create_time"
)
;
//创建默认分区
CREATE TABLE IF NOT EXISTS SWAP_DATA_INFO_DEFAULT PARTITION OF SWAP_DATA_INFO DEFAULT;
--索引
CREATE INDEX "idx_partition_table_name_1" ON "public"."partition_table_name" USING btree ("data_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "idx_partition_table_name_2" ON "public"."partition_table_name" USING btree ("tenant_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "idx_partition_table_name_3" ON "public"."partition_table_name" USING btree ("create_time" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);ALTER TABLE "public"."partition_table_name" ADD CONSTRAINT "pk_partition_table_name" PRIMARY KEY ("id","create_time");--创建分区表函数
CREATE OR REPLACE FUNCTION public.create_daypartition_by_day(v_tablename character varying, v_start_day character varying, v_end_day character varying)RETURNS voidLANGUAGE plpgsql
AS $function$
DECLARE vt_i integer;  vd_i integer; vn_day date; vn_next_day date; vn_num_day varchar;  vn_day_num integer;
BEGINselect count(tablename) into vt_i from  pg_tables where tablename=v_tablename||'_'||to_char(v_end_day :: DATE, 'yyyyMMdd');if vt_i = 0 thenselect (v_end_day::date - v_start_day::date) into vn_day_num;
for vd_i in 0..vn_day_num loop
vn_day := v_start_day :: DATE + (vd_i || ' day') :: INTERVAL ;vn_next_day := vn_day + INTERVAL '1 day' ;vn_num_day := to_char(vn_day, 'yyyyMMdd') ; EXECUTE 'create table ' || v_tablename || '_' || vn_num_day || ' partition of ' || v_tablename || ' for values from (''' || vn_day || ' 00:00:00'') to (''' || vn_next_day || ' 00:00:00'');' ;
end loop;else  RAISE EXCEPTION 'partition table % exist!',v_tablename ||'_' ||v_start_day; end if;
END ; $function$;set lock_timeout = 9999999;--执行创建分区表函数
select create_daypartition_by_day('partition_table_name','2022-07-01','2022-09-30');
select create_daypartition_by_day('partition_table_name','2022-10-01','2022-12-01');--历史数据放进默认分区
INSERT INTO partition_table_name SELECT * FROM partition_table_name_bak;