Upsert with multiply onConflict

[Minhnhat0408]

So I have a reactions table like this

And i want to upsert if user click 'up' or 'down' (type) each reactions can should have at least post_id or user_id different from each other (each user can only have on react on the post)
I come across the solution with upsert (insert when not exist update when existed) with onConflict ) and a stackoverflow post about multiple constrant for it:

but for my case it doesn't work even if I had already set post_id and user_id tobe unique in the table. It still give me the error:"there is no unique or exclusion constraint matching the ON CONFLICT specification"

hope you guys can help me did I do anything wrong

[GaryAustin1]

I believe both columns have to be part of a unique constraint, not each separately.
https://www.tutorialsteacher.com/postgresql/unique-constraint

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE(column1,column2);

[maxhsu-da]

can this be done in the UI of supabse?

[GaryAustin1]

Not the table UI. The SQL editor allows you to run the code above.

[Bisqvi]

If I'm understanding what you're trying to achieve, you can (with the Supabase UI) check these two columns as your primary key, rather than having a separate UUID (if you really mean that there should only be one row in the table for each combo of user_id & post_id). For me, onConflict based on those two columns works then!

[GaryAustin1]

Yes, If you want them to both be the primary key then there will be a constraint.