I have a table:

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

I want to move users.company_name to a new companies table, with users.company_id referencing companies.id. Preferably, I'd like to do this in one transaction.

This expresses what I want conceptually, but isn't valid SQL:

BEGIN;

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

-- 2: add users.company_id -> companies.id
ALTER TABLE users
ADD COLUMN company_id INT
CONSTRAINT users_company_id_fk REFERENCES companies (id);

-- 3: move users.company_name to companies.name; update FK
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
  -- this isn't valid; RETURNING can't reference users
  RETURNING companies.id, users.id AS user_id
) AS inserted_companies;

-- 4: drop users.company_name
ALTER TABLE users 
DROP COLUMN company_name;

COMMIT;

Similar questions that don't quite help:

• Error : ERROR: table name specified more than once
• How do I move a column (with contents) to another table in a Rails migration?
• Adding a LEFT JOIN on a INSERT INTO....RETURNING

CodePudding user response：

create table companies
(
    id           serial primary key ,
    company_name text
);
insert into companies (company_name)
select distinct company_name
from users
where company_name is not null;

alter table users add company_id int null;
update users set company_id = companies.id
from companies where companies.company_name = users.company_name;

ALTER TABLE users
DROP COLUMN company_name;

ALTER TABLE users
ADD CONSTRAINT users_company_id_fk foreign key (company_id) REFERENCES companies (id);

DBFiddle demo

CodePudding user response：

Building off @Cetin.Basoz's excellent answer, here's what I ended up with:

-- 1: add companies:
CREATE TABLE companies
(
    id           serial primary key,
    company_name text
);

-- 2: move users.company_name to companies.name
-- using the users.id as the companies.id for the initial import:
INSERT INTO companies (id, company_name)
SELECT id, company_name
FROM users
WHERE company_name IS NOT NULL;

-- 3: update the companies PK index
-- so we don't try to insert duplicate IDs:
SELECT setval('companies_id_seq', (SELECT MAX(id) FROM companies));

-- 4: add references to the newly-inserted companies:
ALTER TABLE users ADD company_id int null;
UPDATE users
SET company_id = users.id
WHERE company_name IS NOT NULL;

-- 5: drop the vestigial users.company_name
ALTER TABLE users DROP COLUMN company_name;

-- 6: add a FK index:
ALTER TABLE users
ADD CONSTRAINT users_company_id_fk
FOREIGN KEY (company_id)
REFERENCES companies (id);

The companies table will have holes in it, but as long as the id space is sufficiently large, I don't see that as a problem. It would have holes if records were deleted.