Clarification on use of useUpsertMutation

[ga-hoog]

Describe the bug
I am trying to update a row using useUpsertMutation in a table with a unique constraint on two columns. I provide these two columns as the primary key to useUpsertMutation, yet the mutation tries to insert a new row, resulting in a "duplicate key value violates unique constraint".

To Reproduce
I have a table
create table test_table (
id int not null primary key,
column_a text,
column_b text,
column_c text,

unique(column_a,column_b)
);
The table contains row (1, a, b, c)

const {
mutateAsync,
} = useUpsertMutation(
supabase.from("test_table"),
["column_a", "column_b"],
null,
{
onSuccess: () => {
console.log("success")
},
}
);

mutateAsync({column_a: 'a', column_b: 'b', column_c: 'd'})

This gets the error {"code": "23505", "details": "Key (column_a, column_b)=('a','b') already exists.", "hint": null, "message": "duplicate key value violates unique constraint "test_column_a_column_b_key""}

Expected behavior
I would expect the mutateAsync to update the existing row rather than insert a new row.

Additional context
Updating the same row using useUpdateMutation works as expected.

const {
mutateAsync,
} = useUpdateMutation(
supabase.from("test_table"),
["column_a", "column_b"],
null,
{
onSuccess: () => {
console.log("success")
},
}
);

[psteinroe]

hey, thanks for opening the issue! useUpsertMutation only works on primary keys. you might be able to hack this by providing the onConflict option, but I would recommend doing the upsert via a custom mutation hook, and then use useUpsertItem helper hook to update the cache afterwards with the new id.