CODEWITHSUNDEEP
supabaseunique-constraintupsert
zahabba
zahabba

Reputation: 3231

Supabase not allowing upsert to table with composite uniqueness constraint

I have a (Supabase/Postgres) table table_x with foreign_key and data_index columns that, together, form a composite uniqueness constraint. I added the constraint to table_x via the below query:

ALTER TABLE table_x
ADD CONSTRAINT table_x_foreignkey_dataindex_uniq UNIQUE (foreign_key, data_index);

In my application, I'm trying to use supabase.upsert() to either add a new row if a row with the foreign_key/data_index combination doesn't exist, or overwrite that row if it does (a 3rd column, data, would hold the updated value I care about).

This is what the call looks like in my app:

supabase
 .from('table_x')
 .upsert([dataToSave],{
    onConflict: 'foreign_key, data_index',
    ignoreDuplicates: false
  });

(I also tried structuring onConflict as an array of strings and ignoreDuplicates as true, and neither change altered the outcome.)

On upsert, I get this error message:

error: {
    code: '23505',
    details: 'Key (foreign_key, data_index)=(5a3515ca-576d-481a-b55a-acxxxxx, 21) already exists.',
    hint: null,
    message: 'duplicate key value violates unique constraint "table_x_foreignkey_dataindex_uniq"'
  },
  data: null,
  count: null,
  status: 409,
  statusText: 'Conflict'

I'm not sure how to have it only allow 1 row with the foreign_key and data_index combination, so that upsert attempts overwrite that row with the updated data.

Upvotes: 0

Views: 114

Answers (0)

Related Questions