PostgreSQL按日期创建分区表报错：函数需标记为IMMUTABLE如何解决？

错误原因 #

PostgreSQL要求分区键表达式必须是IMMUTABLE（不可变）函数，to_char(ccc, 'DD')属于STABLE函数（结果可能随数据库区域设置等参数变化），不符合分区键的要求，因此触发报错。

正确实现方案 #

方案1：使用extract(DAY FROM ...)作为分区键 #

extract(DAY FROM timestamp)是IMMUTABLE函数，可直接用于分区键，返回数值类型的日份（1~31），更适配范围分区逻辑：

1. 创建主表（需调整主键，将分区键纳入其中）：

CREATE TABLE test (
    aaa varchar(4) NOT NULL, 
    bbb int4 NOT NULL,
    ccc timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "PK_test" PRIMARY KEY (aaa, bbb, ccc) -- 分区键必须包含在主键内，保证全局唯一性
) PARTITION BY RANGE (extract(DAY FROM ccc));

2. 创建各日份分区：

-- 1日数据分区（包含1日，不包含2日）
CREATE TABLE test_01 PARTITION OF test FOR VALUES FROM (1) TO (2);
-- 2日数据分区
CREATE TABLE test_02 PARTITION OF test FOR VALUES FROM (2) TO (3);
...
-- 31日数据分区（用MAXVALUE覆盖无后续日份的情况）
CREATE TABLE test_31 PARTITION OF test FOR VALUES FROM (31) TO (MAXVALUE);

方案2：自定义IMMUTABLE函数获取日份字符串 #

如果必须使用字符串格式的日份（如'01'~'31'），可以创建一个明确标记为IMMUTABLE的自定义函数：

1. 创建自定义函数：

CREATE OR REPLACE FUNCTION get_day_str(t timestamp) 
RETURNS varchar(2) 
LANGUAGE sql 
IMMUTABLE 
AS $$
    SELECT to_char(t, 'DD');
$$;

2. 创建主表（主键需包含分区键）：

CREATE TABLE test (
    aaa varchar(4) NOT NULL, 
    bbb int4 NOT NULL,
    ccc timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "PK_test" PRIMARY KEY (aaa, bbb, ccc)
) PARTITION BY RANGE (get_day_str(ccc));

3. 创建分区：

CREATE TABLE test_01 PARTITION OF test FOR VALUES FROM ('01') TO ('02');
CREATE TABLE test_02 PARTITION OF test FOR VALUES FROM ('02') TO ('03');
...
CREATE TABLE test_31 PARTITION OF test FOR VALUES FROM ('31') TO ('99'); -- 用大于'31'的字符串作为排他上限

关键注意事项 #

• 分区表的主键必须包含分区键，否则PostgreSQL无法保证全局主键唯一性（不同分区可能出现重复主键值）。
• 范围分区的TO值是排他性的，因此31日分区的上限需要设置为比'31'大的字符串（方案2）或MAXVALUE（方案1）。
• 优先选择方案1，数值类型的范围分区比字符串类型性能更优，且无需维护自定义函数。

内容的提问来源于stack exchange，提问作者Hyeonjun Jeong