基于另一张表创建行并将新生成UUID同步至父表的实现疑问

完全可以通过单次操作实现，不需要分开执行插入和更新语句。核心是利用PostgreSQL的**CTE（公共表表达式）**结合INSERT ... RETURNING语法，捕获插入时生成的UUID，再关联更新products表。

具体实现代码 #

WITH inserted_inventory AS (
    INSERT INTO inventory (id, product_sku)
    SELECT 
        gen_random_uuid(),
        p.sku
    FROM products p
    WHERE p.id NOT IN (SELECT id from products where inventory_id IS NULL)
    RETURNING id, product_sku
)
UPDATE products p
SET inventory_id = ii.id
FROM inserted_inventory ii
WHERE p.sku = ii.product_sku;

逻辑说明 #

1. 首先通过WITH子句创建临时结果集inserted_inventory，执行原有的插入操作，并通过RETURNING返回刚插入的id（生成的UUID）和对应的product_sku。
2. 紧接着利用这个临时结果集，关联products表的sku字段，将匹配记录的inventory_id更新为对应的UUID。

优化建议 #

如果products表的sku不是唯一约束，建议改用product_id关联更可靠（避免同SKU多产品导致更新错误）。可以调整语句如下：

WITH inserted_inventory AS (
    INSERT INTO inventory (id, product_sku)
    SELECT 
        gen_random_uuid(),
        p.sku
    FROM products p
    WHERE p.id NOT IN (SELECT id from products where inventory_id IS NULL)
    RETURNING id, product_sku
)
UPDATE products p
SET inventory_id = ii.id
FROM inserted_inventory ii
JOIN products p_match ON p_match.sku = ii.product_sku
WHERE p.id = p_match.id;

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