Supabase - Upsert & multiple onConflict constraints

Question

I cannot figure out how to proceed with an Upsert & "multiple" onConflict constraints. I want to push a data batch in a Supabase table.

My data array would be structured as follows:

items = [
    { date: "2023-01-26", url: "https://wwww.hello.com"}, 
    { date: "2023-01-26", url: "https://wwww.goodbye.com"}, 
    ...]

I would like to use the Upsert method to push this new batch in my Supabase table, unless if it already exists. To check if it already exists, I would like to use the date, and the url as onConflict criteria, if I understood well.

When I'm running this method

const { error } = await supabase
        .from('items')
        .upsert(items, { onConflict: ['date','url'] })
        .select();

I'm having the following error:

{
  code: '42P10',
  details: null,
  hint: null,
  message: 'there is no unique or exclusion constraint matching the ON CONFLICT specification'
}

What am I missing? Where am I wrong?

Mansueli · Accepted Answer · 2023-01-26T16:05:52.740

7

You can pass more than one column in the upsert into by adding a column in a string (instead of using an array):

const { data, error } = await supabase
  .from('items')
  .upsert(items, { onConflict: 'date, url'} )

Postgres performs unique index inference as mentioned in https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

It is necessary to have unique or indexes for this to work, as you can read in the documentation above:

INSERT into tables that lack unique indexes will not be blocked by concurrent activity. Tables with unique indexes might block if concurrent sessions perform actions that lock or modify rows matching the unique index values being inserted; the details are covered in Section 64.5. ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error.

edited Jan 26 '23 at 16:05

answered Jan 26 '23 at 15:33

Mansueli

6,223
8
33
57

ok, I still have the same error using that syntax... does the onConflict keys have to be unique? – Henri Jan 26 '23 at 15:42
2

Yes, that's also necessary as you can read in the docs linked in my answer. I'll update the answer to make this more explicit. – Mansueli Jan 26 '23 at 16:04
Any thoughts on handling a partial index? The CONFLICT condition would be something like `ON CONFLICT (col1, col2) WHERE col3=true` to have Postgres match the partial unique index on `col1,col2 WHERE col3=true`. – vick Mar 13 '23 at 20:44

Supabase - Upsert & multiple onConflict constraints

1 Answers1