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;