【数据库】Postgresql、PG的分区操作：创建、删除指定分区，非分区表转分区表

〇、参考链接

一、为表创建指定分区

-- 表创建分区 参数  表名 分区序列  例如: ltc_customer , 20220915 则创建 ltc_customer_20220915 分区表

CREATE or replace FUNCTION createPartitionIfNotExists (tb_name VARCHAR, partiton_val VARCHAR) RETURNS void AS $body$

  DECLARE master_name TEXT := tb_name; -- 创建分区表的表名

  DECLARE partition_name TEXT := tb_name || '_' || partiton_val; -- 分区的表名称

  BEGIN

    -- 判断分区名称是否存在，不存在时才需要创建

    IF to_regclass (partition_name) IS NULL THEN

      -- 执行创建分区

      EXECUTE format (

          'create table %I partition of %I for values in (%s)',

          partition_name,

          master_name,

          partiton_val

      );

    END IF;

  END;

$body$ LANGUAGE plpgsql;

二、删除指定的表分区

-- 删除表分区 例如: ltc_customer_20220915

CREATE or replace FUNCTION deletePartitionIfExists (tb_name_partiton_val VARCHAR) RETURNS void AS $body$

  DECLARE master_name TEXT := tb_name_partiton_val; -- 删除分区表 表名

  BEGIN

    -- 判断分区名称是否存在，不存在时才需要创建

    IF to_regclass (tb_name_partiton_val) is not null THEN

      -- 执行创建分区

      EXECUTE format ('DROP TABLE IF EXISTS %s ',tb_name_partiton_val);

    END IF;

  END;

$body$ LANGUAGE plpgsql;

三、将原有的非分区表转换为分区表

-- 将原有的非分区表转化为分区表

CREATE OR REPLACE FUNCTION "table_migration"("tb_name" varchar, "partition_column" varchar)

  RETURNS "pg_catalog"."void" AS $BODY$ DECLARE

DECLARE

  old_name TEXT := tb_name || '_old';

declare d varchar;

declare days varchar[] :=   array(select to_char(date(t),'yyyymmdd')::varchar as day

 from

 generate_series('2022-08-01'::date,'2022-11-01', '1 days') as t );

BEGIN

  IF

    to_regclass ( tb_name ) IS NOT NULL THEN

      -- 1. 表重命名

      EXECUTE format ( 'ALTER TABLE %s RENAME TO %s', tb_name, old_name );

      -- 2. 创建分区表

      EXECUTE format ( 'CREATE TABLE %s ( LIKE %s INCLUDING ALL ) PARTITION BY list ( %s )', tb_name, old_name, partition_column );

      -- 3. 创建2022年一年的分区

      foreach d in array days loop

        EXECUTE format ( 'create table %s partition of %s for values in (%s)', tb_name || '_' || d, tb_name, d );

      end loop;

      -- 4. 执行数据迁移

      EXECUTE format ( 'insert into %s select * from %s', tb_name, old_name );

      -- 5. 删除原来的表，可以先不做

      -- EXECUTE format ( 'drop table %s',  old_name );

  END IF;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

【数据库】Postgresql、PG的分区操作：创建、删除指定分区，非分区表转分区表的相关教程结束。