Supabase•3y ago

Trying to insert in bulk fails if one of the items already exists

I'm trying to create an entry if one like it doesn't already exist (i.e. if there's no UNIQUE conflict). I want to do this in bulk (it's for inviting users to a project) but if any of the rows conflicts, the whole op fails. I tried using .insert(payload) as well as .upsert(payload, {ignoreDuplicates: true}) and both had the same issue... I'm using supabase-js (with Next.js)

7 Replies

jdgamble555•3y ago

What errors do you get?

HunterAndersonXOP•3y ago

{
  upsertMembersData: null,
  upsertMembersError: {
    code: '23505',
    details: 'Key (project_id, user_id)=(e598620f-2455-44a0-a1e3-5df2a4bca8a7, 5d840f27-6998-4d22-92c1-8ce3566b6453) already exists.',
    hint: null,
    message: 'duplicate key value violates unique constraint "members_project_id_user_id_key"'
  }
}

{
  upsertMembersData: null,
  upsertMembersError: {
    code: '23505',
    details: 'Key (project_id, user_id)=(e598620f-2455-44a0-a1e3-5df2a4bca8a7, 5d840f27-6998-4d22-92c1-8ce3566b6453) already exists.',
    hint: null,
    message: 'duplicate key value violates unique constraint "members_project_id_user_id_key"'
  }
}

which would be fine if the other items in the set were created successfully... same error when using .upsert(payload, {ignoreDuplicates: true})

jdgamble555•3y ago

try { onConflict: 'unique id' }

jdgamble555•3y ago

https://supabase.com/docs/reference/javascript/upsert

Upsert data: upsert() | Supabase

Performs an UPSERT into the table.

HunterAndersonXOP•3y ago

{
  upsertMembersData: null,
  upsertMembersError: {
    code: '42703',
    details: null,
    hint: null,
    message: 'column "unique id" does not exist'
  }
}

{
  upsertMembersData: null,
  upsertMembersError: {
    code: '42703',
    details: null,
    hint: null,
    message: 'column "unique id" does not exist'
  }
}

jdgamble555•3y ago

replace 'unique id' with your unique id column

HunterAndersonXOP•3y ago

success! thank you! .upsert(payload, {onConflict: 'project_id, user_id'}) did the trick

Gaming

Programming

Trying to insert in bulk fails if one of the items already exists

Did you find this page helpful?