社区博客 | PostgreSQL按日期列创建分区表

CREATE TABLE test (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE test_y2024m01 PARTITION OF test
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
 
CREATE TABLE test_y2024m02 PARTITION OF test
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE OR REPLACE FUNCTION create_partition_and_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- 替换为动态生成分区名和分区范围的逻辑
    IF NEW.logdate >= '2024-01-01' AND NEW.logdate < '2024-02-01' THEN
        CREATE TABLE IF NOT EXISTS test_y2024m01 PARTITION OF test
            FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
        RETURN NEW;
    ELSIF NEW.logdate >= '2024-02-01' AND NEW.logdate < '2024-03-01' THEN
        CREATE TABLE IF NOT EXISTS test_y2024m02 PARTITION OF test
            FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
        RETURN NEW;
    -- 添加更多条件以处理其他时间段
    ELSE
        RAISE EXCEPTION 'Date out of range. No partition available for %', NEW.logdate;
    END IF;
END;
$$ LANGUAGE plpgsql;
 
 
CREATE TRIGGER insert_test_partition
    AFTER INSERT
    ON public.test
    FOR EACH ROW
    EXECUTE FUNCTION public.create_partition_and_insert();