New member
- Joined
- Feb 14, 2023
- Messages
- 7
- Thread Author
- #1
I have table revaluation_of_table with unique constraint (item,date,sales_category). I have to update or insert data from stock_revaluation table. So I tried these code.
This show stock_revaluation table doesn't have duplicate. So i wrote this code to obtain result.
But I receive this error. ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification. Help me to solve this
I saw this:- No unique or exclusion constraint matching the ON CONFLICT but didn't found solution
Code:
select item,date,sales_category from arpan.stock_revaluation
group by item,date,sales_category
having count(item)>1
Code:
INSERT INTO arpan.revaluation_of_table_test (
item
,date
,purchase
,price
,other_adjustments
,stock
,sold
,sold_from_stock
,sold_from_purchase
,total_remaining
,remaining_stock
,remaining_purchase
,realised
,reserved
,"Total_value_stock"
,month
,sales_category
)
SELECT item
,date
,purchase
,price
,other_adjustments
,stock
,sold
,sold_from_stock
,sold_from_purchase
,total_remaining
,remaining_stock
,remaining_purchase
,realised
,reserved
,"Total_value_stock"
,month
,sales_category
FROM arpan.stock_revaluation ON CONFLICT (item, date, sales_category) DO UPDATE
SET item = excluded.item
,date = excluded.date
,purchase = excluded.purchase
,price = excluded.price
,other_adjustments = excluded.other_adjustments
,stock = excluded.stock
,sold = excluded.sold
,sold_from_stock = excluded.sold_from_stock
,sold_from_purchase = excluded.sold_from_purchase
,total_remaining = excluded.total_remaining
,remaining_stock = excluded.remaining_stock
,remaining_purchase = excluded.remaining_purchase
,realised = excluded.realised
,reserved = excluded.reserved
,"Total_value_stock" = excluded."Total_value_stock"
,month = excluded.month
,sales_category = excluded.sales_category
But I receive this error. ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification. Help me to solve this
I saw this:- No unique or exclusion constraint matching the ON CONFLICT but didn't found solution