The scenario is as described in the following steps (this is only an example to illustrate the problem)

1. There are two tables in the database (user_1, post_1) - no real relation between them

user_1 contains the following fields:

  id VARCHAR,   
  name VARCHAR,  
  address TEXT,  
  phone_number VARCHAR,  
  PRIMARY KEY (id)

post_1 contains the following fields:

  id VARCHAR,   
  user_id VARCHAR,  
  title VARCHAR,  
  body TEXT,  
  PRIMARY KEY (id)

2. Suppose I added the following data to the two tables above:

    INSERT INTO user_1(id, name, address, phone_number) VALUES ('first_u', 'avi', 'some address', '05488789906');
   
    INSERT INTO post_1(id, user_id, title, body) VALUES ('first_p', 'first_u', 'new post', 'This is a good one!');
   

3. Now I've created new tables with few changes and with a link between them:

user_2 contains the following fields:

  id uuid DEFAULT uuid_generate_v4(),   
  name VARCHAR,  
  address TEXT,  
  phone_number VARCHAR,  
  PRIMARY KEY (id)

post_2 contains the following fields:

  id uuid DEFAULT uuid_generate_v4(),   
  user_id uuid,  
  title VARCHAR,  
  body TEXT,  
  PRIMARY KEY (id),
  CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES user_2(id)

4. Now the problem is with the copy of the data to the two new tables with the real link (foreign key) between them. I will explain - it is divided into few parts:

a. Count the number of rows in the user_1 table (I've created a function for that):

CREATE OR REPLACE FUNCTION rowsnumber() RETURNS INTEGER AS $$
DECLARE
numberOfRows integer;
BEGIN
        SELECT COUNT(*) INTO numberOfRows FROM (
                    SELECT *
                    FROM user_1
            ) t;
            
RETURN numberOfRows;
END;
$$ LANGUAGE plpgsql;

b. Go through the function result in the for loop and do the following:

• (b.1) Extract the row number x according to the current index in the for loop (can achieve this by using limit and offset) from user_1 table.
• (b.2) Insert the row data into the user_2 table.
• (b.3) Extract the newly created uuid and save it in a parameter.
• (b.4) Retrieve the appropriate row from the post_1 table according to the id column of the current row stored in the user_id column of the post_1 table.
• (b.5) Insert the appropriate data of the row that we extracted in stage (b.4) into the post_2 table BUT with the user_id that we extracted in step (b.3) so that there is a real relationship between the post_2 table and the user_2 table.

I would greatly appreciate any help - if someone could write the query I would need to run to solve this problem. Many thanks.

CodePudding user response：

A possible solution. Not tested, so I would recommend testing on your end first.

CREATE TABLE user_2(
  id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,   
  id_old varchar,
  name VARCHAR,  
  address TEXT,  
  phone_number VARCHAR)

CREATE TABLE post_2 (
  id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id_old varchar, 
  user_id uuid,  
  title VARCHAR,  
  body TEXT
)


INSERT INTO user_2(id_old, name, address, phone_number)  SELECT id,name, address, phone_number FROM user_1;

INSERT INTO post_2(user_id_old, title, body) SELECT user_id, title, body FROM post_1;

UPDATE post_2 SET user_id = id FROM user_2 WHERE post_2.user_id_old = user_2.id_old;

ALTER TABLE post_2 ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES user_2 (id);

ALTER TABLE user_2 DROP COLUMN id_old;

ALTER TABLE post_2 DROP COLUMN user_id_old;

CodePudding user response：

After several attempts I was able to implement the function that would answer the question - according to the steps I described above.

CREATE OR REPLACE FUNCTION insertToTables() RETURNS VOID AS $$
DECLARE
numOfRowsInTable integer := rowsnumber();
newRow record;
userUUIDRow record;
postRow record;
BEGIN
         for r in 0..numOfRowsInTable-1
         loop
           SELECT * INTO newRow FROM user_1 LIMIT 1 OFFSET r;
           INSERT INTO user_2(name, address, phone_number) SELECT newRow.name,newRow.address, newRow.phone_number ;
           SELECT * INTO userUUIDRow FROM user_2 ORDER BY created_at DESC LIMIT 1;
           SELECT * INTO postRow FROM post_1 WHERE user_id = newRow.id;
           INSERT INTO post_2(user_id, title, body) SELECT userUUIDRow.id, postRow.title, postRow.body;
         end loop;
END;
$$ LANGUAGE plpgsql;