PostgreSQL 11拆分属性列时如何为缺失字段补NULL值？

要实现将oldattributes和newattributes拆分行，同时保留oldattributes中所有属性（即使newattributes中不存在）并对应显示NULL，需要将两个字段的键值对拆分后做左连接，以oldattributes的属性键为基准。具体SQL语句如下：

WITH old_kv AS (
    SELECT
        t1.ctid, -- 用ctid标识原表的每一行，避免同表多数据时混淆
        split_part(kv_pair, '=', 1) AS attr_key,
        split_part(kv_pair, '=', 2) AS old_attr_value
    FROM t1,
         LATERAL regexp_split_to_table(t1.oldattributes, ',') kv_pair
),
new_kv AS (
    SELECT
        t1.ctid,
        split_part(kv_pair, '=', 1) AS attr_key,
        split_part(kv_pair, '=', 2) AS new_attr_value
    FROM t1,
         LATERAL regexp_split_to_table(t1.newattributes, ',') kv_pair
)
SELECT
    ok.attr_key,
    ok.old_attr_value,
    nk.new_attr_value
FROM old_kv ok
LEFT JOIN new_kv nk ON ok.ctid = nk.ctid AND ok.attr_key = nk.attr_key
ORDER BY ok.ctid, ok.attr_key;

逻辑说明 #

1. CTE old_kv：将oldattributes按逗号拆分为单个键值对行，再用split_part拆分出属性键（attr_key）和旧值（old_attr_value），同时保留原表行的ctid用于关联。
2. CTE new_kv：同理拆分newattributes得到属性键和新值。
3. 左连接：以old_kv为左表，通过ctid（原表行标识）和attr_key（属性键）关联new_kv，确保oldattributes中的所有属性都被保留，newattributes中不存在的属性对应new_attr_value为NULL。

预期输出 #

执行上述SQL后，针对示例数据，DEPARTMENT对应的new_attr_value会显示为NULL，完整输出如下：

注意事项 #

• 使用ctid是为了处理原表存在多行数据的场景，确保不同行的属性不会混淆；如果原表有主键，也可以用主键替代ctid。
• split_part函数在键值对中没有=符号时会返回空字符串，符合原数据的存储逻辑；如果需要将空字符串转为NULL，可以用NULLIF(split_part(...), '')处理。

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