How to split table into two tables in one transaction or query?
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.