Michael的笔记本

原表已经有5000万数据了，不能直接在原表上分区，推荐另外创建一张分区表，采用数据库函数（或者java代码）将分区实现建好（不建议触发器创建分区），然后将原表的数据写入分区表。

新建分区表并迁移数据（推荐）

-- 1. 创建新的分区表结构
CREATE TABLE events_partitioned (
    id SERIAL,
    event_data TEXT,
    event_time_tz VARCHAR(28),
    event_time_utc TIMESTAMPTZ GENERATED ALWAYS AS (
        to_timestamp(event_time_tz, 'YYYY-MM-DD HH24:MI:SS.MS TZH')
    ) STORED
) PARTITION BY RANGE (event_time_utc);

-- 2. 预先创建所需分区
SELECT create_monthly_partitions_tz('events_partitioned', '1984-01-01'::date , '2028-01-01'::date,'+08');

-- 3. 分批次迁移数据（避免锁表）
INSERT INTO events_partitioned (id, event_data, event_time_tz)
SELECT id, event_data, event_time_tz FROM events
WHERE to_timestamp(event_time_tz, 'YYYY-MM-DD HH24:MI:SS.MS TZH') 
BETWEEN '2020-01-01' AND '2020-02-01';

-- 4. 最后重命名表（需要停机维护）
BEGIN;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_partitioned RENAME TO events;
COMMIT;

自动创建分区的函数（批量创建）

-- 批量创建分区的函数（按本地时区）
CREATE OR REPLACE FUNCTION create_monthly_partitions_tz(
    table_name text,
    start_date date, 
    end_date date, 
    timezone_offset text
)
RETURNS void AS $$
DECLARE
    current_date_val date;  -- 重命名变量，避免关键字冲突
    partition_name text;
    partition_start text;
    partition_end text;
BEGIN
    current_date_val := start_date;
    WHILE current_date_val <= end_date LOOP
        partition_name := table_name || '_' || to_char(current_date_val, 'YYYYMM');
  
        -- 使用更安全的时间格式转换
        partition_start := to_char(current_date_val, 'YYYY-MM-DD') || ' 00:00:00.000 ' || timezone_offset;
        partition_end := to_char(
            (current_date_val + interval '1 month')::date, 
            'YYYY-MM-DD'
        ) || ' 00:00:00.000 ' || timezone_offset;
  
        -- 使用更安全的format语句
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)', 
            partition_name,
            table_name, 
            partition_start, 
            partition_end
        );
  
        current_date_val := current_date_val + interval '1 month';
    END LOOP;
END;
$$ LANGUAGE plpgsql;