move all values from column in one table to new table and update third table with relation between those tables, PostgreSQL

Question

I am using SQL after a long time and I have following:
I have existing table1 with columns id, name and a lot of other columns, it already contains rows.
I created empty table2 which only has columns id and name.
I created empty table3 which only has reference columns table1_id and table2_id.

Now I want to:

1. take all the values from column name in table1 (can be NULL, discard them in that case),
2. insert them as new rows into table2,
3. insert ids of the corresponding table1 and table2 rows into table3,
4. remove the column name from table1.
   => probably ALTER TABLE table1 DROP COLUMN name;, but I guess there may be a neater way to cut the result from step 1, transform it and paste as rows in step 2.

EDIT: I came up with something like (not tested yet):

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;

EDIT: I forgot to tell that if the name already existed in table2, I don't want to add it again (should be unique in table2), but I add the relation between the id from table1 and from the inserted/existing id from table2 into the table3.

EDIT: I found we have source scripts for creating the database and I changed it there. Now I don't know how to get rid of this open question :(

Answer 1

For steps 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;

Step 3). Not sure of the purpose of table3 as you would have matching id values between table1 and table2. In fact you could use that to set up a FOREIGN KEY relationship between them.

Step 4) Your solution: ALTER TABLE table1 DROP COLUMN name;

Answer 2

Not sure how you want to use it. If you want to run it as one-time transformation in one bulk, this could help (you can try the code on 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;

This could also be useful:

• stackoverflow_1
• postgresqltutorial
• stackoverflow_2
• postgresql documentation with PL/pgSQL code - suggestion you wrote in question is going much more this way