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