使用Postgres插入数据并设置外键

Question

我需要在 Postgres 数据库中进行架构更改后，迁移大量现有数据。

在旧的架构中，国家属性会存储在用户表中。现在，国家属性已经被移动到一个单独的地址表中：

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;

然而，尽管在用户.id和地址.user_id上使用了索引，但这种方法在执行过程中非常缓慢。

用户表包含约300万行数据，其中有30万行没有相关联的地址信息。

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

我正在使用Postgres 8.3.14。

谢谢

我现在通过使用Python/sqlalchemy脚本迁移数据来解决了该问题。结果证明这比尝试使用SQL完成同样的操作要容易得多（对我而言）。不过，如果有人知道如何处理Postgres SQL中INSERT语句的RETURNING结果，请告诉我，我很感兴趣。

- Pankrat

---

这是旧的问题，你已经解决了。但在这种情况下，1:1关系没有意义。难道你不应该创建一个国家表吗？ - Clodoaldo Neto

一个地址实际上包含每个用户的街道、城市、邮政编码等信息，还有国家。我只是简化了它以使其更易读。 - Pankrat

国家、邮政编码、城市、县等都将拥有自己的表格。这样留下了街道、门牌号等内容。但是，除非每个用户可以拥有多个地址，否则没有必要为此单独设置一个表格。 - Clodoaldo Neto

Answer 1

表users必须有一些你没有披露的主键。为了回答这个问题，我将其命名为users_id。

你可以使用在PostgreSQL9.1中引入的修改数据的公共表表达式(CTEs)来优雅地解决此问题：

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已经到达生命周期的尽头。

不管怎样，在版本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;

由于无法将恰好一个ID与具有相同国家的每个用户集合一一对应，因此我使用窗口函数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

重复此过程，直到users.address_id中的最后一个NULL值消失。

- Erwin Brandstetter

---

非常感谢！从你的回答中学到了很多新东西。是的，我们同时升级到了Postgres 9.1。干杯！ - Pankrat

@Pankrat：这是个好消息——既有助于解决问题，又能升级到9.1版本。 - Erwin Brandstetter