将所有值从一个表中的列移动到新表，并使用这些表之间的关系更新第三个表，PostgreSQL

Question

我在很长一段时间后使用SQL，我有以下内容：
我有现有的table1列id，name和许多其他列，它已经包含行.
我创建了空table2，其中只有列id和name.
我创建了空table3，其中只有引用列table1_id和table2_id。

现在我想：

1. 从table1中获取第name列中的所有值(可以是 NULL，在这种情况下丢弃它们)，
2. 将它们作为新行插入table2，
3. 将相应table1和table2行的id插入到table3中，
4. table1从 .
   =中删除列name>可能ALTER TABLE table1 DROP COLUMN name;，但我想可能有更整洁的方法可以剪切步骤 1 中的结果，对其进行转换并在步骤 2 中粘贴为行。

编辑：我想出了类似的东西(尚未测试)：

SELECT table1.id, table1.name INTO results FROM table1;
FOR result1 IN
results
LOOP
WITH result2 AS (
INSERT INTO table2 (name) VALUES (result1.name) RETURNING id
)
INSERT INTO table3 (table2_id, table1_id) VALUES (result2.id, result1.id);
END LOOP;
ALTER TABLE table1 DROP COLUMN name;

编辑： 我忘了告诉name如果table2已经存在，我不想再次添加它(在表2中应该是唯一的)，但是我添加了来自table1的id和从table2插入/现有的id之间的关系table3。

编辑： 我发现我们有用于创建数据库的源脚本，我在那里更改了它。现在我不知道如何摆脱这个悬而未决的问题:(

Answer 1

对于步骤 1) & 2)：

--Since you already have a table2
DROP TABLE table2;
--Create new table2 with data. Unless you are going to replace NULL with something 
--discarding them would just end up with NULL again.
CREATE table2 AS SELECT id, name from table1;

步骤 3)。不确定表 3 的用途，因为表 1 和表 2 之间会有匹配的id值。事实上，你可以用它来在它们之间建立FOREIGN KEY关系。

步骤4)您的解决方案：ALTER TABLE table1 DROP COLUMN name;

Answer 2

不确定如何使用它。如果你想一次性批量运行它，这可能会有所帮助(你可以在sqlfiddle上尝试代码)：

CREATE TABLE table1 (
id int,
name varchar(9)
);
INSERT INTO table1 (
id,
name
)
VALUES
(1, 'A'),
(2, null),
(3, 'C'),
(4, null),
(5, 'E'),
(6, 'C')
;
CREATE TABLE table2 (
id SERIAL,
name varchar(9) UNIQUE
);
INSERT INTO table2 (name)
SELECT DISTINCT name
FROM table1
WHERE name IS NOT NULL
;
/*
-- This would be better option, but I was not able to test the merge/upsert function of PostgreSQL
INSERT INTO table2 (name)
SELECT name
FROM table1
WHERE name IS NOT NULL
ON CONFLICT table2_name_key DO NOTHING --merge/upsert, supports PostgreSQL 9.5 and newer
;
*/
CREATE TABLE table3 (
id_table1 int,
id_table2 int
) AS
SELECT
t1.id id_table1,
t2.id id_table2
FROM table1 t1
INNER JOIN table2 t2
ON t1.name = t2.name
;
--ALTER TABLE table1 DROP COLUMN name;

这也很有用：

• stackoverflow_1
• Postgresqltutorial
• stackoverflow_2
• 带有PL/pgSQL代码的postgresql文档 - 您编写的建议将更多地以这种方式进行