How to split table into two tables in one transaction or query?

Question

I have a table "profiles" with these columns: "id", "name", "surname", "email", "avatar".

I want to create table "media" which will contain columns "id" and "path".

After that I want to create column "profiles"."avatar_id" and migrate data by this rule: each value of "profiles"."avatar" must be inserted into "media" ("media"."id" is autogenerated), returning id must be written into "profiles"."avatar_id" in the same row.

It should be executed in one query, transaction or procedure, and I want to use only postgres functionality.

I tried to rename "profiles" to "profiles_old", create new "profiles" with "avatar_id" but without "avatar". Then run something like:

INSERT INTO profiles(id, "name", email, surname, avatar_id)
SELECT id, "name", email, surname, (INSERT INTO media(origin) VALUES(profiles_old.avatar) RETURNING id) FROM profiles_old;

...but SELECT doesn't support subquery INSERT. Maybe I should use procedure?

I am using Postgres 9.4.9. I tried a transaction and temp tables. I cannot understand how to insert each row of one table into another table and update the same row of first table with returned id? My code:

BEGIN; 
ALTER TABLE profiles DROP CONSTRAINT profiles_pkey; 
ALTER TABLE profiles RENAME TO profiles_old; 
CREATE TABLE profiles ( 
id uuid NOT NULL DEFAULT uuid_generate_v4(), 
"name" character varying(255), 
email character varying(255), 
surname character varying(255), 
avatar_id uuid NOT NULL, 
CONSTRAINT profiles_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); 
ALTER TABLE profiles OWNER TO app;
INSERT INTO profiles(id, "name", email, surname, gender, avatar_id) SELECT id, "name", email, surname, (INSERT INTO media(origin) VALUES(profiles_old.avatar) RETURNING id) FROM profiles_old; 
DROP TABLE profiles_old; 
COMMIT;

Answer 1

I cannot understand how to insert each row of one table into another table and update the same row of first table with returned id

You don't have to use an intermediate table. It can be done in one statement, with a writeable CTE:

WITH t as
 (INSERT INTO media(origin) SELECT DISTINCT avatar FROM profile RETURNING id,origin)  
 UPDATE profile SET avatar_id=t.id FROM t WHERE avatar=t.origin;

(assuming that media.id is automatically set by its DEFAULT clause).

Or the same as two statements in a transaction, using a different form of update:

BEGIN;
INSERT INTO media(origin) SELECT DISTINCT avatar FROM profile;
UPDATE profile SET avatar_id=(SELECT id FROM media WHERE profile.avatar=media.origin);
COMMIT;

If you know that profile.avatar is unique, DISTINCT can be omitted, otherwise it's required.