PostgreSQL按可空列拆分表为分区，如何避免使用INSERT INTO及报错

问题背景 #

有一张海量数据表source_aepsfinswitch_tmp_details，需要按reconciliation_date_time列拆分为两个分区：

• 当reconciliation_date_time为NULL时，进入一个分区
• 当reconciliation_date_time不为NULL时，进入另一个分区

要求避免使用INSERT INTO迁移数据，尝试以下两种方法均报错：

第一次尝试（RANGE分区） #

执行SQL：

BEGIN;
CREATE TABLE IF NOT EXISTS public.source_aepsfinswitch_tmp_details_new (
    LIKE public.source_aepsfinswitch_tmp_details EXCLUDING CONSTRAINTS,
    PRIMARY KEY (uniqueid, reconciliation_date_time)
) PARTITION BY RANGE (reconciliation_date_time);

ALTER TABLE public.source_aepsfinswitch_tmp_details RENAME TO source_aepsfinswitch_tmp_details_old;
ALTER TABLE public.source_aepsfinswitch_tmp_details_new RENAME TO source_aepsfinswitch_tmp_details;
ALTER TABLE public.source_aepsfinswitch_tmp_details ATTACH PARTITION public.source_aepsfinswitch_tmp_details_old DEFAULT;
COMMIT;

报错：

SQL Error [42804]: ERROR: column "reconciliation_date_time" in child table must be marked NOT NULL

第二次尝试（LIST分区） #

执行SQL：

CREATE TABLE IF NOT EXISTS public.source_aepsfinswitch_tmp_details_new (LIKE public.source_aepsfinswitch_tmp_details EXCLUDING CONSTRAINTS) PARTITION BY LIST (reconciliation_date_time);    
ALTER TABLE public.source_aepsfinswitch_tmp_details RENAME TO source_aepsfinswitch_tmp_details_old;
ALTER TABLE public.source_aepsfinswitch_tmp_details_new RENAME TO source_aepsfinswitch_tmp_details;
ALTER TABLE public.source_aepsfinswitch_tmp_details ATTACH PARTITION public.source_aepsfinswitch_tmp_details_old DEFAULT;
CREATE TABLE IF NOT EXISTS public.source_aepsfinswitch_tmp_details_null PARTITION OF public.source_aepsfinswitch_tmp_details FOR VALUES IN (NULL);

报错：

SQL Error [23514]: ERROR: updated partition constraint for default partition "source_aepsfinswitch_tmp_details_old" would be violated by some row

尝试基于is_processed列的TRUE/FALSE值分区时，同样出现重叠或行冲突错误。

解决方案 #

核心问题分析 #

1. RANGE分区报错原因：创建父表时指定的主键包含reconciliation_date_time，主键列强制要求NOT NULL，但原表中该列允许NULL，挂载时约束冲突。
2. LIST分区报错原因：默认分区已包含所有NULL和非NULL数据，创建NULL分区时出现约束重叠，违反分区不重叠规则。

推荐方案：表达式LIST分区（高效无数据拷贝） #

利用PostgreSQL的表达式分区特性，将分区键设为reconciliation_date_time IS NULL（生成布尔值TRUE/FALSE，无NULL值），既满足分区需求，又避免主键约束冲突。

步骤1：创建分区父表与子分区

BEGIN;
-- 创建父表，基于布尔表达式分区
CREATE TABLE public.source_aepsfinswitch_tmp_details_new (
    LIKE public.source_aepsfinswitch_tmp_details INCLUDING ALL
) PARTITION BY LIST ((reconciliation_date_time IS NULL));

-- 创建NULL值对应分区（表达式结果为TRUE）
CREATE TABLE public.source_aepsfinswitch_tmp_details_null
    PARTITION OF public.source_aepsfinswitch_tmp_details_new
    FOR VALUES IN (TRUE);

-- 创建非NULL值对应分区（表达式结果为FALSE）
CREATE TABLE public.source_aepsfinswitch_tmp_details_non_null
    PARTITION OF public.source_aepsfinswitch_tmp_details_new
    FOR VALUES IN (FALSE);

步骤2：重命名原表并挂载为默认分区

-- 重命名原表
ALTER TABLE public.source_aepsfinswitch_tmp_details RENAME TO source_aepsfinswitch_tmp_details_old;

-- 重命名父表为原表名
ALTER TABLE public.source_aepsfinswitch_tmp_details_new RENAME TO source_aepsfinswitch_tmp_details;

-- 挂载原表为默认分区
ALTER TABLE public.source_aepsfinswitch_tmp_details ATTACH PARTITION public.source_aepsfinswitch_tmp_details_old DEFAULT;

步骤3：拆分默认分区数据到对应子分区

-- 创建临时表存储NULL数据
CREATE TABLE public.source_aepsfinswitch_tmp_details_null_temp
    (LIKE public.source_aepsfinswitch_tmp_details_old INCLUDING ALL);

-- 批量移动NULL数据到临时表（比INSERT INTO高效）
WITH moved_rows AS (
    DELETE FROM public.source_aepsfinswitch_tmp_details_old
    WHERE reconciliation_date_time IS NULL
    RETURNING *
)
INSERT INTO public.source_aepsfinswitch_tmp_details_null_temp
SELECT * FROM moved_rows;

-- 给原表添加非NULL约束，符合non_null分区规则
ALTER TABLE public.source_aepsfinswitch_tmp_details_old
ADD CONSTRAINT chk_non_null CHECK (reconciliation_date_time IS NOT NULL);

-- 交换原表到non_null分区（无数据拷贝）
ALTER TABLE public.source_aepsfinswitch_tmp_details
EXCHANGE PARTITION source_aepsfinswitch_tmp_details_non_null
WITH TABLE public.source_aepsfinswitch_tmp_details_old;

-- 给临时表添加NULL约束，符合null分区规则
ALTER TABLE public.source_aepsfinswitch_tmp_details_null_temp
ADD CONSTRAINT chk_null CHECK (reconciliation_date_time IS NULL);

-- 交换临时表到null分区（无数据拷贝）
ALTER TABLE public.source_aepsfinswitch_tmp_details
EXCHANGE PARTITION source_aepsfinswitch_tmp_details_null
WITH TABLE public.source_aepsfinswitch_tmp_details_null_temp;

COMMIT;

步骤4：清理临时表

DROP TABLE public.source_aepsfinswitch_tmp_details_old;
DROP TABLE public.source_aepsfinswitch_tmp_details_null_temp;

替代方案：继承表（适配PostgreSQL 10及以下版本） #

如果不支持表达式分区，可使用继承表实现类似效果：

BEGIN;
-- 创建父表
CREATE TABLE public.source_aepsfinswitch_tmp_details_new (
    LIKE public.source_aepsfinswitch_tmp_details INCLUDING ALL
);

-- 创建NULL值子表
CREATE TABLE public.source_aepsfinswitch_tmp_details_null (
    CHECK (reconciliation_date_time IS NULL)
) INHERITS (public.source_aepsfinswitch_tmp_details_new);

-- 创建非NULL值子表
CREATE TABLE public.source_aepsfinswitch_tmp_details_non_null (
    CHECK (reconciliation_date_time IS NOT NULL)
) INHERITS (public.source_aepsfinswitch_tmp_details_new);

-- 重命名原表
ALTER TABLE public.source_aepsfinswitch_tmp_details RENAME TO source_aepsfinswitch_tmp_details_old;

-- 重命名父表为原表名
ALTER TABLE public.source_aepsfinswitch_tmp_details_new RENAME TO source_aepsfinswitch_tmp_details;

-- 移动数据到子表
WITH moved_rows AS (
    DELETE FROM public.source_aepsfinswitch_tmp_details_old
    WHERE reconciliation_date_time IS NULL
    RETURNING *
)
INSERT INTO public.source_aepsfinswitch_tmp_details_null
SELECT * FROM moved_rows;

INSERT INTO public.source_aepsfinswitch_tmp_details_non_null
SELECT * FROM public.source_aepsfinswitch_tmp_details_old;

-- 清理原表并设置主键与约束排除
DROP TABLE public.source_aepsfinswitch_tmp_details_old;
ALTER TABLE public.source_aepsfinswitch_tmp_details_null ADD CONSTRAINT pk_null PRIMARY KEY (uniqueid);
ALTER TABLE public.source_aepsfinswitch_tmp_details_non_null ADD CONSTRAINT pk_non_null PRIMARY KEY (uniqueid);
ALTER TABLE public.source_aepsfinswitch_tmp_details ADD CONSTRAINT pk_parent PRIMARY KEY (uniqueid);
SET constraint_exclusion = on;

COMMIT;

内容的提问来源于stack exchange，提问作者Purushottam Nawale