PostgreSQL 分割表建立步驟

1. 建立分區表結構

CREATE TABLE new_table_name (LIKE original_table) 
PARTITION BY RANGE (date_column);

2. 自動建立月份分區

DO $$
DECLARE
    start_date DATE := 'start_date';  -- 例：'2020-01-01'
    end_date DATE := CURRENT_DATE;
    curr_date DATE;
BEGIN
    curr_date := start_date;

    WHILE curr_date < end_date LOOP
        EXECUTE format(
            'CREATE TABLE %s_%s PARTITION OF %s
            FOR VALUES FROM (%L) TO (%L)',
            '[table_name]',
            to_char(curr_date, 'YYYY_MM'),
            '[new_table_name]',
            curr_date,
            curr_date + INTERVAL '1 month'
        );
        curr_date := curr_date + INTERVAL '1 month';
    END LOOP;
END $$;

-- 建立未來分區
CREATE TABLE table_name_future PARTITION OF new_table_name DEFAULT;

3. 遷移數據和替換表

INSERT INTO [new_table_name] SELECT * FROM [original_table];
ALTER TABLE [original_table] RENAME TO [original_table_old];
ALTER TABLE [new_table_name] RENAME TO [original_table];
INSERT INTO new_table_name SELECT * FROM original_table;
ALTER TABLE original_table RENAME TO original_table_old;
ALTER TABLE new_table_name RENAME TO original_table;

自動增加分割表

DO $$
DECLARE
    last_partition_date DATE;
    target_end_date DATE := CURRENT_DATE + INTERVAL '6 months'; -- 預建立未來6個月的分表
    curr_date DATE;
    partition_exists boolean;
BEGIN
    -- 找出最後一個分區的日期（排除 future 分區）
    SELECT to_date(replace(split_part(partition_name, '_', -2) || '_' || 
                          split_part(partition_name, '_', -1), '_', ''), 'YYYYMM')
    INTO last_partition_date
    FROM (
        SELECT inhrelid::regclass::text AS partition_name
        FROM pg_inherits
        WHERE inhparent = 'your_table_name'::regclass
        AND inhrelid::regclass::text NOT LIKE '%future'
        ORDER BY inhrelid::regclass::text DESC
        LIMIT 1
    ) subq;

    -- 設定起始日期為最後一個分區的下一個月
    curr_date := last_partition_date + INTERVAL '1 month';

    WHILE curr_date <= target_end_date LOOP
        -- 檢查分區是否已存在
        SELECT EXISTS (
            SELECT 1
            FROM pg_inherits
            WHERE inhparent = 'your_table_name'::regclass
            AND inhrelid::regclass::text = format('your_table_name_%s', to_char(curr_date, 'YYYY_MM'))::text
        ) INTO partition_exists;

        -- 如果分區不存在，則建立新分區
        IF NOT partition_exists THEN
            EXECUTE format(
                'CREATE TABLE your_table_name_%s PARTITION OF your_table_name
                FOR VALUES FROM (%L) TO (%L)',
                to_char(curr_date, 'YYYY_MM'),
                curr_date,
                curr_date + INTERVAL '1 month'
            );
            RAISE NOTICE '已建立分區 your_table_name_%', to_char(curr_date, 'YYYY_MM');
        END IF;

        curr_date := curr_date + INTERVAL '1 month';
    END LOOP;
END $$;

處理 future 分區中已經存在了日期無法新增問題

-- 1. 首先檢查 future 分區中的數據分布
SELECT date_trunc('month', tr_date) as month,
       count(*) as record_count
FROM your_table_name_future
GROUP BY date_trunc('month', tr_date)
ORDER BY month;

-- 2. 建立臨時表來存儲要移動的數據
CREATE TEMP TABLE temp_future_data AS
SELECT *
FROM your_table_name_future
WHERE tr_date >= '2024-12-01'
  AND tr_date < '2025-01-01';

-- 3. 從 future 分區中刪除這些數據
DELETE FROM your_table_name_future
WHERE tr_date >= '2024-12-01'
  AND tr_date < '2025-01-01';

-- 4. 現在可以建立新的分區
CREATE TABLE your_table_name_2024_12 
PARTITION OF your_table_name
FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

-- 5. 將臨時表中的數據插入新分區
INSERT INTO your_table_name
SELECT * FROM temp_future_data;

-- 6. 清理臨時表
DROP TABLE temp_future_data;