Welcome!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

SignUp Now!

Postgres:- No unique constraint in on conflict do update

New member
Joined
Feb 14, 2023
Messages
7
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.

Code:
select item,date,sales_category from arpan.stock_revaluation
group by item,date,sales_category
having count(item)>1
This show stock_revaluation table doesn't have duplicate. So i wrote this code to obtain result.

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
 
Top