0

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;
  • 1
    Is it a strict 1-1 relationship? I.e. will every row in existing table correspond to 1 row in the new table? And vice versa? If yes, why do you need/want the media.id to be autogenerated? Why can't you just copy the profile.id values and get rid of the avatar_id and all this complexity? – ypercubeᵀᴹ Oct 6 '16 at 22:53
  • Because media will contain not only avatars but another media records which not related with profiles. I solved my problem with Node.js because I didn't find how to do this by SQL or postgres script. – abr_stackoverflow Oct 7 '16 at 12:46
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.