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;
media.id
to be autogenerated? Why can't you just copy theprofile.id
values and get rid of theavatar_id
and all this complexity? – ypercubeᵀᴹ Oct 6 '16 at 22:53