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?

Answer 1

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.