PostgreSQL CTE返回ID无法在UPDATE的WHERE子句中生效问题

原查询的问题出在UPDATE语句的FROM子句关联逻辑上：你在更新accessory_variants表的同时，FROM子句里同时引用了同表的插入结果CTE variant和聚合后的ids，虽然两者都是单行，但这种逗号分隔的写法会生成笛卡尔积，且PostgreSQL在处理目标表与同表CTE的关联时，容易出现匹配失效的问题。

修正后的查询写法1（推荐，逻辑更清晰） #

WITH variant AS (
    INSERT INTO accessory_variants (
        accessory_id,
        label,
        multiple
    ) VALUES (
        1,
        'Colors',
        FALSE
    ) RETURNING *
),
opts AS (
    INSERT INTO accessory_variant_options (
        accessory_id,
        accessory_variant_id,
        price,
        label,
        description
    ) VALUES (
        1,
        (SELECT id FROM variant),
        100,
        'Red',
        'A red one'
    ),
    (
        1,
        (SELECT id FROM variant),
        100,
        'Blue',
        'A blue one'
    ) RETURNING id
)
UPDATE accessory_variants
SET ordering = (SELECT jsonb_agg(id) FROM opts)
WHERE id = (SELECT id FROM variant);

修正后的查询写法2（保留JOIN关联） #

WITH variant AS (
    INSERT INTO accessory_variants (
        accessory_id,
        label,
        multiple
    ) VALUES (
        1,
        'Colors',
        FALSE
    ) RETURNING *
),
opts AS (
    INSERT INTO accessory_variant_options (
        accessory_id,
        accessory_variant_id,
        price,
        label,
        description
    ) VALUES (
        1,
        (SELECT id FROM variant),
        100,
        'Red',
        'A red one'
    ),
    (
        1,
        (SELECT id FROM variant),
        100,
        'Blue',
        'A blue one'
    ) RETURNING id, accessory_variant_id
)
UPDATE accessory_variants av
SET ordering = (SELECT jsonb_agg(id) FROM opts WHERE opts.accessory_variant_id = av.id)
FROM variant v
WHERE av.id = v.id;

核心优化点 #

1. 移除了多余的ids CTE，直接在UPDATE的SET子句中聚合opts的ID，简化逻辑。
2. 把原FROM子句的逗号分隔写法，改成直接在WHERE子句中通过子查询定位目标行，或者用明确的表别名+JOIN关联，避免同表关联时的逻辑歧义。
3. 确保UPDATE的WHERE条件能精准匹配到CTE中刚插入的variant行，解决原查询中匹配失效的问题。

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