Supabase•2y ago

Upsert with 2 conditions

I have a tool that keeps stats, every day for an organisation it keeps a set of data. However using the following query, it overwrites the row, even when the date changes:

const { error } = await db.from('absence_analytics').upsert({
        org_id: org_id,
        absence_count: finalized_crawl.meta.absence_count,
        unique_absences: finalized_crawl.meta.unique_absences,
        crawl_timestamp: finalized_crawl.meta.crawl_timestamp,
        date: date,
        period_1: finalized_crawl.data.period_1 as unknown as Json[]
///.. more rows
    });

const { error } = await db.from('absence_analytics').upsert({
        org_id: org_id,
        absence_count: finalized_crawl.meta.absence_count,
        unique_absences: finalized_crawl.meta.unique_absences,
        crawl_timestamp: finalized_crawl.meta.crawl_timestamp,
        date: date,
        period_1: finalized_crawl.data.period_1 as unknown as Json[]
///.. more rows
    });

14 Replies

Olyno•2y ago

Hi :vmathi: Could the following code solves your issues?

try {
 const result = await db.from('absence_analytics').insert({
   org_id: org_id,
   absence_count: finalized_crawl.meta.absence_count,
   unique_absences: finalized_crawl.meta.unique_absences,
   crawl_timestamp: finalized_crawl.meta.crawl_timestamp,
   date: date,
   period_1: finalized_crawl.data.period_1 as unknown as Json[]
   // .. more rows
 });
} catch (error) {
 if (error.code === '23505') { // unique_violation
   await db.from('absence_analytics')
     .where('org_id', org_id)
     .andWhere('date', date)
     .update({
       absence_count: finalized_crawl.meta.absence_count,
       unique_absences: finalized_crawl.meta.unique_absences,
       crawl_timestamp: finalized_crawl.meta.crawl_timestamp,
       period_1: finalized_crawl.data.period_1 as unknown as Json[]
       // .. more rows
     });
 } else {
   throw error;
 }
}

try {
 const result = await db.from('absence_analytics').insert({
   org_id: org_id,
   absence_count: finalized_crawl.meta.absence_count,
   unique_absences: finalized_crawl.meta.unique_absences,
   crawl_timestamp: finalized_crawl.meta.crawl_timestamp,
   date: date,
   period_1: finalized_crawl.data.period_1 as unknown as Json[]
   // .. more rows
 });
} catch (error) {
 if (error.code === '23505') { // unique_violation
   await db.from('absence_analytics')
     .where('org_id', org_id)
     .andWhere('date', date)
     .update({
       absence_count: finalized_crawl.meta.absence_count,
       unique_absences: finalized_crawl.meta.unique_absences,
       crawl_timestamp: finalized_crawl.meta.crawl_timestamp,
       period_1: finalized_crawl.data.period_1 as unknown as Json[]
       // .. more rows
     });
 } else {
   throw error;
 }
}

MaxTechnicsOP•2y ago

That doesn't seem to entirely work, however i think i have found a workaround. I thought i was doing something wrong with upsert. However i can check if the row exists, and update it, otherwise insert. I don't have a unique constraint on the org_id and date column since multiple points should be able to be saved for an org, and multiple orgs should be able to save on the same date. Thanks for getting me started though!! :crumbLove:

Socal•2y ago

you should have an onConflict create a unique index on what you determine to be the best way to identify a unique entry

await supabaseClient.schema(schema).from(table).upsert(updateData.data, { onConflict: on_conflict });

await supabaseClient.schema(schema).from(table).upsert(updateData.data, { onConflict: on_conflict });

in this case it's a comma delimited string of field names. the field names are in a unique index on the table

 on_conflict = 'tenant_id, id'

 on_conflict = 'tenant_id, id'

MaxTechnicsOP•2y ago

Oh, didn't know you could do it that way, thank you very much!

Socal•2y ago

just remember to create the unique index in the database

MaxTechnicsOP•2y ago

Yeah i'm probably going to make a unique computed column that combines org id and date. And go from there.

Socal•2y ago

you don't need a computed column

MaxTechnicsOP•2y ago

i can't set a unique constraint on the org_id or the date though

Socal•2y ago

just do it on org_id you want the ones existing to be updated, right?

MaxTechnicsOP•2y ago

Only if the date is still the same every day there are about 6 pushes to the service, only the latest for said date should be kept, but other days need to create new rows

Socal•2y ago

example

 CREATE UNIQUE INDEX idx_tablename_ukey ON schema.table USING btree (tenant_id, id);

 CREATE UNIQUE INDEX idx_tablename_ukey ON schema.table USING btree (tenant_id, id);

so instead of tenant_id , id you would just do org_id, date for an upsert it will only update if that org_id, date exists. otherwise it will be an insert that is what an upsert is supposed to do

MaxTechnicsOP•2y ago

yeah that's what i expected upsert to do, though onConflict requires a unique column

Socal•2y ago

no, you can have multiple columns your unique index needs to match your onconflict

MaxTechnicsOP•2y ago

Oh i see

Gaming

Programming

Upsert with 2 conditions

Did you find this page helpful?