0

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

1
  • Maybe you want a trigger to do it automatically for all updates? Sep 21, 2020 at 9:55

1 Answer 1

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.