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 void
 LANGUAGE 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;
BEGIN
        select count(tablename) into vt_i from  pg_tables where tablename=v_tablename||'_'||to_char(v_end_day :: DATE, 'yyyyMMdd');
        if vt_i = 0 then

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