'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 ;
If user buys more shares tomorrow and before ex_date and payable_date, for example, 1000sh FB then
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|


