How to do an SQL UPDATE based on INSERT RETURNING id in Postgres?

Question

I'm using Postgres. I have an old table and a new table. The records in the old table need to have related records in the new table. The new table records are effectively going to act as the parents for records in the new table.

I'm trying to write a migration where each "child" from the old table will have "parent" in the new table.

OLD TABLE (I've added a foreign key in anticipation of populating it as part of the migration)

 id | name | new_id (FK)
----+------+-------------
  1 | 1st  | 
  2 | 2nd  |

NEW TABLE

 id | name 
----+------
    |

I need to do the following:

SELECT all records from the old table
INSERT a record into the new table for each old record and RETURNING id
UPDATE the old record's foreign key value with the RETURNING id

Is there a way to accomplish this using a set query? Or do I need to start delving into Postgres specific things like LOOPs?

You can do this in Postgres. The question would be much easier to follow with sample data and results. — Gordon Linoff
– Gordon Linoff, Commented Apr 10, 2018 at 22:06
currval() if you know sequence name, lastval() for last insert in sequence, to alternate between select/insert/update in each loop for original rows, or RETURNING id method. — Nerdi.org
– Nerdi.org, Commented Apr 10, 2018 at 22:08
I'm not too great with Postgres, but I wanted to do some research. This page should help you greatly. stackoverflow.com/questions/23382865/… — Nerdi.org
– Nerdi.org, Commented Apr 10, 2018 at 22:29

Michel Milezzi · Accepted Answer · 2018-04-11 11:08:41Z

6

You can use a writable cte as follows:

--open transaction
BEGIN;

--avoid concurrent writes
LOCK old_table IN ROW SHARE MODE;
LOCK new_table IN ROW SHARE MODE;

--create a sequence to populate new_table.id
CREATE SEQUENCE new_table_seq;

--using a writable cte to do the dirty job
WITH old_table_cte AS (
    UPDATE old_table SET new_id = nextval('new_table_seq') RETURNING *
)
INSERT INTO new_table SELECT new_id, name FROM old_table_cte;

--Create a proper FK
ALTER TABLE old_table ADD CONSTRAINT old_table_new_table_fk FOREIGN KEY (new_id) REFERENCES new_table (id);

--end transaction
COMMIT;

This approach has some benefits:

Taking advantage of RETURNING as you asked, but in update step. Avoiding an unnecessary extra SELECT;
Concurrency safe;
Postponing FK creation will result in a faster script;
Using pure SQL solution, no need to use procedural language;
Avoiding read and write to old_table at same step.

edited Apr 11, 2018 at 11:08

answered Apr 10, 2018 at 22:25

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nerdi.org · Accepted Answer · 2018-04-10 22:08:01Z

0

This page should help you out :) postgreSQL function for last inserted ID

INSERT INTO tableName (column1, column2) VALUES ('column1val', 'column2val') RETURNING id;

You can then use the returned id to update the original row. You can also use a few other methods suggested on that SO page (mentioned below / linked above)

currval() if you know sequence name, lastval() for last insert in sequence, to alternate between select/insert/update in each loop for original rows, or RETURNING id method.

answered Apr 10, 2018 at 22:08

Nerdi.org

9336 silver badges13 bronze badges

2 Comments

Soviut Over a year ago

I know about RETURNING id but I need to do this for every record in my old table. It's how I should iterate over the records in the old table that I'm stumped on.

Nerdi.org Over a year ago

You need to use sequences to do what you want to do with Postgresql

JERRY · Accepted Answer · 2018-04-11 04:20:28Z

-1

Use TEMP Table to perform migration. Before update on main table, we should manipulate data over TEMP table as requirement.

answered Apr 11, 2018 at 4:20

JERRY

1,1581 gold badge8 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to do an SQL UPDATE based on INSERT RETURNING id in Postgres?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related