Return the pre and post UPDATE column values of a specific row in PostgreSQL
I have a table with columns user_id, item_id, and test.
I’m running a query in Node.js that needs to return the value of the ‘test’ column for the updated row BEFORE and AFTER the update took place (in some kind of tuple format, maybe).
The query I am running is:
`INSERT INTO testTable (user_id, item_id, test) VALUES($1, $2, $3) ON CONFLICT (user_id, item_id) DO UPDATE SET test = EXCLUDED.test`
Essentially, I need to try to insert, unless the row with the passed in user_id/item_id pairing already exists, in which case I need to update that row with a new value for ‘test’. The problem I’m having is that the ‘test’ column is a Boolean, and depending on if it goes from T -> F, F -> T, F -> F, or T -> T, I need it to do something different. Not sure how to handle this in the SQL statement. I was thinking a return statement but not sure how to access the before/after values of the columns.
Source: JavaSript – Stack OverflowNovember 28, 2021