Executing an insert query on successful deletion in postgres

user_13566273 0
Viewed 48

Is it possible to insert some data into one table if some entry is deleted in another table?

I am able to do that with multiple queries(delete first, then insert by checking if it exists). But this runs as two queries. Is it possible to merge the queries or what is the most efficient way of doing this?

postgresql sql
2 Answers
WITH deleted_rows AS (
   DELETE FROM users 
   WHERE
      id = 15
   RETURNING *
)
INSERT INTO users_history (SELECT * FROM deleted_rows);
user_172960840

I explain my answer:

RETURNING * return table records such as select * from command, but return only deleted records. This RETURNING command you can use after the INSERT or UPDATE command too. The WITH query being CTE (Common Table Expression) query, is particularly useful when the subquery is executed multiple times. It is equally helpful in place of temporary tables. It computes the aggregation once and allows us to reference it by its name (maybe multiple times) in the queries. So finally we use SELECT * FROM deleted_rows query which will return all deleted records and using INSERT INTO users_history query we can insert all these return records to the new user_history table.

user_172960840
Related