[Python] Supabase Python upsert on_conflict for multi-column constraints

I have the following table:

create table public.paper (
title text not null,
abstract text null,
venue text null,
doi text null,
id uuid not null default gen_random_uuid (),

constraint paper_pkey primary key (id),
constraint paper_doi_key unique (doi),
constraint paper_id_key unique (id),
constraint unique_title_abstract unique (title, abstract),
constraint unique_title_venue unique (title, venue)
) TABLESPACE pg_default;


I am scraping papers from a few websites and storing them on Supabase. However, I want to be able to handle duplicates. If there are duplicates, I want to update instead. If there are no other solutions, skipping that row would be fine.

try:
res_papers = (
supabase.table("paper").upsert(papers_without_authors, on_conflict="doi,(title,venue),(title,abstract)").execute()
)
paper_id_map = {paper["title"]: paper["id"] for paper in res_papers.data}
except RuntimeError as e:
raise RuntimeError(f"Error upserting papers: {e}") from e


This resulted in an error: Unexpected error in upsert_papers: {'message': '"failed to parse on_conflict parameter (doi,(title,venue),(title,abstract))" (line 1, column 5)', 'code': 'PGRST100', 'hint': None, 'details': 'unexpected "(" expecting field name (* or [a..z0..9_$])'}

I also tried writing the columns separately: doi,title,venue,abstract.

However, I got the following error: Unexpected error in upsert_papers: {'message': 'there is no unique or exclusion constraint matching the ON CONFLICT specification', 'code': '42P10', 'hint': None, 'details': None}

I couldn't find any information on the documentation for handling multi-column unique constraints.

Continue reading...