Drizzle TeamDT
Drizzle Team•3y ago
SKUZZIE

Upsert with multi-column unique index?

What is the correct way to do an upsert with a multi-column unique index?
I have this model:
export const dailyStats = pgTable(
  "daily_stats",
  {
    ad_id: text("ad_id")
      .references(() => ads.id)
      .primaryKey(),
    created_at: timestamp("created_at").notNull().defaultNow(),
    updated_at: timestamp("updated_at").notNull().defaultNow(),

    date: date("date").notNull(),

    ...statObject,
  },
  (t) => ({
    ad_and_date: uniqueIndex("ad_and_date").on(t.ad_id, t.date),
  })
);


And I'm trying to do an upsert like this:
await db
        .insert(dailyStats)
        .values({
          ad_id: dbAd.id,
          date: date.toISOString(),
          ...dailyStatsInsert,
        })
        .onConflictDoUpdate({
          target: [dailyStats.ad_id, dailyStats.date],
          set: dailyStatsInsert,
        });


But I'm getting the duplicate key value violates unique constraint error even though I have the target of the onConflictDoUpdate set to those two columns.

What am I doing wrong?
Was this page helpful?