PostgreSQL update table then insert on another table

Question

I have a User table and a Log table. I need to update a field on the User table and insert a new entry on the Log table recording the update. I have written the statement for the update:

 UPDATE users SET free_shipping_until = spu.created_at::date + interval '1 year'
 FROM shipping_priority_users AS spu
 WHERE spu.role_id = #{role.id} and users.id = spu.user_id and spu.created_at is not null;

For each update I need to also add (probably in a transaction) an insert statement on the Log table which has the following columns

user_id: string,
created_at: timestamp
data: jsonb

• The data column contains a jsonb value including the free_shipping_until value from the update.

data: {"free_shipping_until": [null, "2021年07月30日"]}

• user_id should match the updated record's value
• The created_at column is the current time, I'm using RoR and could interpolate the value with the expected format.

I'm using PostgreSQL 12.3

• Colin 't Hart: Maybe you want a trigger to do it automatically for all updates?

Answer 1

You can use a data modifying CTE to do this in a single statement:

with changed_user as (
 UPDATE users 
 SET free_shipping_until = spu.created_at::date + interval '1 year'
 FROM shipping_priority_users AS spu
 WHERE spu.role_id = #{role.id} 
 and users.id = spu.user_id 
 and spu.created_at is not null
 returning *
) 
insert into log (user_id, created_at, data)
select id, 
 current_timestamp, 
 jsonb_build_object('free_shipping_until', array[null, free_shipping_until::text])
from changed_user;

from changed_user