sql - 使用 Postgres 插入数据和设置外键

Question

架构更改后，我必须迁移 Postgres 数据库中的大量现有数据。

在旧模式中，国家属性将存储在用户表中。现在 country 属性已移至单独的地址表中:

users:
  country # OLD
  address_id # NEW [1:1 relation]

addresses:
  id
  country

架构实际上更复杂，地址包含的不仅仅是国家/地区。因此，每个用户都需要有自己的地址(1:1 关系)。

迁移数据时，插入地址后在用户表中设置外键时遇到问题:

INSERT INTO addresses (country) 
    SELECT country FROM users WHERE address_id IS NULL 
    RETURNING id;

如何传播插入行的 ID 并在用户表中设置外键引用？

到目前为止，我能想到的唯一解决方案是在地址表中创建一个临时的 user_id 列，然后更新 address_id:

UPDATE users SET address_id = a.id FROM addresses AS a 
    WHERE users.id = a.user_id;

但是，结果证明这非常慢(尽管在 users.id 和 addresses.user_id 上都使用了索引)。

用户表包含大约 300 万行，其中 30 万行缺少关联地址。

是否有任何其他方法可以将派生数据插入一个表并将外键引用设置为另一个表中插入的数据(不更改架构本身)？

我正在使用 Postgres 8.3.14。

谢谢

我现在已经通过使用 Python/sqlalchemy 脚本迁移数据解决了这个问题。事实证明(对我而言)它比尝试使用 SQL 更容易。尽管如此，如果有人知道在 Postgres SQL 中处理 INSERT 语句的 RETURNING 结果的方法，我还是很感兴趣。

Answer 1

users 表必须有一些您没有透露的主键。出于此答案的目的，我将其命名为 users_id。

您可以使用 data-modifying CTEs 相当优雅地解决这个问题随 PostgreSQL 9.1 引入:

country 是唯一的

在这种情况下，整个操作相当简单:

WITH i AS (
    INSERT INTO addresses (country) 
    SELECT country
    FROM   users
    WHERE  address_id IS NULL 
    RETURNING id, country
    )
UPDATE users u
SET    address_id = i.id
FROM   i
WHERE  i.country = u.country;

您在问题中提到了版本 8.3。升级! Postgres 8.3 has reached end of life.

尽管如此，这对于 8.3 版来说已经足够简单了。你只需要两个声明:

INSERT INTO addresses (country) 
SELECT country
FROM   users
WHERE  address_id IS NULL;

UPDATE users u
SET    address_id = a.id
FROM   addresses a
WHERE  address_id IS NULL 
AND    a.country = u.country;

country 不是唯一的

这更具挑战性。您可以只创建一个地址并多次链接到它。但是您确实提到了排除这种方便解决方案的 1:1 关系。

WITH s AS (
    SELECT users_id, country
         , row_number() OVER (PARTITION BY country) AS rn
    FROM   users
    WHERE  address_id IS NULL 
    )
    , i AS (
    INSERT INTO addresses (country) 
    SELECT country
    FROM   s
    RETURNING id, country
    )
    , r AS (
    SELECT *
         , row_number() OVER (PARTITION BY country) AS rn
    FROM   i
    )
UPDATE users u
SET    address_id = r.id
FROM   r
JOIN   s USING (country, rn)    -- select exactly one id for every user
WHERE  u.users_id = s.users_id
AND    u.address_id IS NULL;

因为没有办法明确地将 INSERT 返回的一个 id 分配给一组具有相同 country 的每个用户，我使用窗口函数 row_number()使它们独一无二。

不像 Postgres 8.3 那样直接。一种可能的方式:

INSERT INTO addresses (country) 
SELECT DISTINCT country -- pick just one per set of dupes
FROM   users
WHERE  address_id IS NULL;

UPDATE users u
SET    address_id = a.id
FROM   addresses a
WHERE  a.country = u.country
AND    u.address_id IS NULL
AND NOT EXISTS (
    SELECT * FROM addresses b
    WHERE  b.country = a.country
    AND    b.users_id < a.users_id
    ); -- effectively picking the smallest users_id per set of dupes

重复此操作，直到最后一个 NULL 值从 users.address_id 中消失。