CODEWITHSUNDEEP
sqlpostgresqlsql-updatesql-insertcommon-table-expression
Rocket Appliances
Rocket Appliances

Reputation: 377

Update table with returned id from insert on other table

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.

Upvotes: 2

Views: 1836

Answers (2)

nonethewiser
nonethewiser

Reputation: 618

A normalized approach would be to put the user_id in the metadata which also solves this problem for saving the id on the related resource (you just insert it with the other user.info field).

I was having this same problem. Initially I opted for a solution where I set a temporary user_id column on metadata to facilitate putting the metadata id on users but ultimately realized a more normalized approach would generally be better for me (as well as sidestepping this issue). In particular, if there is no metadata you have to store it as null. It's workable but ultimately I found it simpler to have user_id on metadata.

Upvotes: 0

GMB
GMB

Reputation: 222632

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.

Demo on DB Fiddle

Upvotes: 6

Related Questions