如何迁移users表company_name至新companies表并更新引用关系？

现有users表数据如下：

select * from users
id | name |   company_name
 1 |  Sam | Sam's Plumbing
 2 |  Pat |   Pat's Bakery
 3 |  Vic |

需要将users表的company_name字段迁移到新创建的companies表中，同时让users表新增的company_id字段关联companies表的id字段，并且整个操作要在单个事务中完成。

我尝试写了如下SQL来实现预期逻辑，但无法执行：

BEGIN;

-- 1: 新建companies表
CREATE TABLE companies (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name varchar(255) not null
);

-- 2: 给users表添加关联字段company_id
ALTER TABLE users
ADD COLUMN company_id INT
CONSTRAINT users_company_id_fk REFERENCES companies (id);

-- 3: 将users.company_name迁移到companies.name，并更新外键关联
UPDATE users
SET users.company_id = inserted_companies.id
FROM (
  INSERT INTO companies (name)
  SELECT company_name FROM users
  WHERE company_name IS NOT NULL
  -- 这里不合法：RETURNING无法引用users表的字段
  RETURNING companies.id, users.id AS user_id
) AS inserted_companies;

-- 4: 删除users表的company_name字段
ALTER TABLE users 
DROP COLUMN company_name;

COMMIT;

我查阅了几个类似问题，但都没能解决我的需求：

• 错误：ERROR: table name specified more than once
• 如何在Rails迁移中将一个带内容的字段移到另一个表？
• 在INSERT INTO....RETURNING中添加LEFT JOIN

正确解决方案 #

问题核心在于：INSERT...RETURNING语句只能返回被插入表（companies）的字段，无法直接关联原users表的id，所以没法在插入时直接拿到用户和新公司的对应关系。以下是两种可行的事务化实现方案：

方案1：基础版（假设公司名唯一）

适合业务场景中公司名不会重复的情况，通过唯一约束避免重复插入，再通过公司名关联更新用户外键：

BEGIN;

-- 1. 创建companies表，添加唯一约束避免重复公司
CREATE TABLE companies (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name varchar(255) NOT NULL UNIQUE
);

-- 2. 给users表添加company_id字段，允许为空（适配无公司的用户）
ALTER TABLE users
ADD COLUMN company_id INT REFERENCES companies(id);

-- 3. 将所有非空的公司名插入companies表
INSERT INTO companies(name)
SELECT DISTINCT company_name FROM users WHERE company_name IS NOT NULL;

-- 4. 通过company_name关联，更新users表的company_id
UPDATE users
SET company_id = companies.id
FROM companies
WHERE users.company_name = companies.name;

-- 5. 删除原company_name字段
ALTER TABLE users DROP COLUMN company_name;

COMMIT;

方案2：支持重复公司名的版本

如果业务允许同一公司名存在多条记录，或者需要严格保留每个用户和新公司的对应关系，可以用CTE来关联用户ID和公司名：

BEGIN;

CREATE TABLE companies (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name varchar(255) NOT NULL
);

ALTER TABLE users
ADD COLUMN company_id INT REFERENCES companies(id);

-- 用CTE先提取用户-公司对应关系，插入后返回新公司ID并关联更新
WITH user_company_mapping AS (
  SELECT id AS user_id, company_name 
  FROM users 
  WHERE company_name IS NOT NULL
), inserted_companies AS (
  INSERT INTO companies(name)
  SELECT company_name FROM user_company_mapping
  RETURNING id, name
)
UPDATE users u
SET company_id = ic.id
FROM inserted_companies ic
JOIN user_company_mapping uc 
  ON uc.company_name = ic.name AND u.id = uc.user_id;

ALTER TABLE users DROP COLUMN company_name;

COMMIT;

关键注意事项 #

1. 事务一致性：所有操作包裹在BEGIN/COMMIT中，任何步骤失败都会自动回滚，保证数据不会处于中间状态。
2. 外键空值处理：添加company_id时不设置NOT NULL，适配像Vic这样没有公司的用户。
3. 重复数据处理：方案1的UNIQUE约束会阻止重复公司名插入，方案2则允许重复，根据业务需求选择。

内容的提问来源于stack exchange，提问作者James A. Rosen