I'm trying to figure out how to insert data from Table1 into Table2, then use the newly-created ID from Table2 to update the corresponding row in Table1.
I'm using Postgres 12.4 for what it's worth
Example:
I've got two tables, e.g. users and metadata
The users tables has the following columns
| id | info | metadata_id |
The metadata table has the following columns
| id | data |
I want to migrate all of my info values from the users table into the data column of the metadata table, and update my users.metadata_id (currently blank) with the corresponding metadata.id values, essentially backfilling foreign keys.
Is there any way to accomplish this gracefully? I've got a working query which locks both tables and creates a temporary sequence to insert into the metadata.id and users.metadata_id but this seems brittle and I would need to start the sequence after the highest-existing ID in the metadata table, which isn't ideal.
I've also tried to use a data-modifying CTE with a RETURNING clause to update the users table, but couldn't get that to work.
You can't use returning here, since you need to keep track of the association of users and metadata while inserting.
I think it is simpler to first pre-generate the metadata serial of each user in a CTE, using nextval(). You can then use that information to insert into metadata and update the users table:
with
candidates as (
select u.*, nextval(pg_get_serial_sequence('metadata', 'id')) new_metadata_id
from users u
),
inserted as (
insert into metadata (id, data) overriding system value
select new_metadata_id, info from candidates
)
update users u
set metadata_id = c.new_metadata_id
from candidates c
where c.id = u.id
We need the overriding system value clause in the insert statement so Postgres allows us to write to a serial column.