'How to do an upsert on computed columns

I have an insert SQL statement and I would like to update the qty when there is a conflict. The unique index is based on user_id, symbol, ex_date, settle_date.

Scenario:

Before the dividend ex_date, the user purchased more shares so instead of inserting a new record, I like to update the qty and the computed columns. Can ON CONFLICT DO UPDATE the best solution? What would be the correct ON CONFLICT DO UPDATE statement?

insert into cash_dividend_details ( 

user_id ,
lastname ,
firstname ,
middlename ,
symbol ,
qty ,
gross_cash_dividend ,
cash_dividend_tax ,
net_cash_dividend ,
dividend_entitled_date ,
ex_date ,
payable_date 

)

(

SELECT s.user_id, 
        v.lastname,
        v.firstname,
        v.middlename,
        s.symbol, 
        s.qty , 
        (s.qty*d.cash_div_rate)::numeric(20,2),
        ((s.qty*d.cash_div_rate)*.10)::numeric(20,2) ,
        (s.qty*d.cash_div_rate)-((s.qty*d.cash_div_rate)*.10)::numeric(20,2) , 
        date(d.ex_date - interval '1 DAY') ,
        date(d.ex_date) , 
        date(d.settle_date) 
FROM stock_portfolio_summary s 
INNER join vw_qbe_vendorslist v on ( v.listid = s.user_id )
INNER join dividend d on ( d.symbol = s.symbol )
WHERE date(s.added_date) <= date(d.ex_date - interval '1 DAY')
  AND s.qty > 0
  AND is_cash_dividend_processed is FALSE
ORDER by d.settle_date asc, user_id 

) ON CONFLICT ( s.user_id, s.symbol, d.ex_date, d.settle_date ) DO
        UPDATE SET ( s.qty .....
--ON CONFLICT DO NOTHING ;

Here's a sample data: enter image description here

If user buys more shares tomorrow and before ex_date and payable_date, for example, 1000sh FB then

enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source