PostgreSQL update table then insert on another table 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: {&quot;free_shipping_until&quot;: [null, &quot;2021-07-30&quot;]}


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
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