bulk insert not processed because of duplicate (using unique index composed by two columns)

[malalecherocks]

Hi, my table:

contacts

id,team_id,name,phone

id(autogenerated uuid)
team_id (it's a foreign key that identifies the team that owns that contact)
name
phone

I have added to this table a UNIQUE INDEX composed by the columns (team_id, phone)
because each team will have several contacts, but always unique.

My problem comes when i have for example the data of one contact, and when I try to import several contacts at once, the insert returns DUPLICATED KEY, because I'm trying to insert a row with the same (team_id, phone) my problem is that the rest of the insert doesn't execute.

According to the docs that's normal, if one insert fails the rest of the insert will be rollback.

My question is... is there any workaround? I've been reading about upsert, but since I'm using a composed UNIQUE INDEX, is that feasable? I did some tests and I can't make it work...

my last solution will be creating a RPC and doing the inserts one by one... but I would like to use it sending the array of rows... and inserted those that aren't duplicated...

Thanks

[steve-chavez]

my last solution will be creating a RPC and doing the inserts one by one... but I would like to use it sending the array of rows... and inserted those that aren't duplicated...

Check the ignoreDuplicates parameter on https://supabase.com/docs/reference/javascript/upsert#parameters

[malalecherocks]

Not suer... I keep receiving the same error:

"duplicate key value violates unique constraint "unique_account_id_phone_international"

This is my code:

` async function test() {
console.log('probando');
const { data, error } = await supabase
.from('contact')
.select('account_id,name,phone_international,phone_local,phone_cc,tags,search,phone_country');

	contacts = data;
	contacts[1].phone_international = '34652225009';

	const { data: data2, error: error2 } = await supabase
		.from('contact')
		.upsert(contacts, { ignoreDuplicates: true });
	data3 = data2;
	error3 = error2;
	console.log(data2, error2);
} `

Keep in mind that the table contact I added a UNIQUE INDEX
CREATE UNIQUE INDEX unique_account_id_phone_international on contact (account_id,phone_international);

Seems to me that ignoreDuplicates only works on when it's a primary key ?? my issue is that a duplicate is a row that has the same (account_id,phone_international)

Any ideas please?

[malalecherocks]

I made it work but removing my primary key uuid, and putting as primary key the (account_id,phone_international) then everything works fine.

[steve-chavez]

Cool. Note that there's also an onConflict parameter where you can specify a list of columns that are part of a constraint.

const { data, error } = await supabase
  .from('users')
  .upsert({ username: 'supabot' }, { onConflict: 'col1,col2' })

https://supabase.com/docs/reference/javascript/upsert#upserting-into-tables-with-constraints

[mahdi-termos]

SQL:

ON CONFLICT (phone, id, whatever unique index combo)-- Replace with the column(s) on which the unique constraint is defined
DO NOTHING;

should skip over rows where duplicates exists and process the next one