Drizzle Team•6mo ago

onConflictDoUpdate error, there is no unique or exclusion constraint matching the ON CONFLICT

export const storespecialhours = pgTable(
  'storespecialhours',
  {
    specialDateID: serial('specialDateID').primaryKey(),
    storeID: integer('storeID')
      .references(() => stores.storeID, { onDelete: 'cascade' })
      .notNull()
      .unique(),
    day: date('day', { mode: 'date' }).notNull().unique(),
    dayOpen: time('dayOpen').notNull().unique(),
    dayClose: time('dayClose').notNull().unique(),
  },
  (storespecialhours) => ({
      unq: unique('dayOpen').on(storespecialhours.storeID, storespecialhours.day),
      unq1: unique('dayClose').on(storespecialhours.storeID, storespecialhours.day),
  }),
)

export const storespecialhours = pgTable(
  'storespecialhours',
  {
    specialDateID: serial('specialDateID').primaryKey(),
    storeID: integer('storeID')
      .references(() => stores.storeID, { onDelete: 'cascade' })
      .notNull()
      .unique(),
    day: date('day', { mode: 'date' }).notNull().unique(),
    dayOpen: time('dayOpen').notNull().unique(),
    dayClose: time('dayClose').notNull().unique(),
  },
  (storespecialhours) => ({
      unq: unique('dayOpen').on(storespecialhours.storeID, storespecialhours.day),
      unq1: unique('dayClose').on(storespecialhours.storeID, storespecialhours.day),
  }),
)

  const [storeHours] = await db
    .insert(storespecialhours)
    .values(openingHours)
    .onConflictDoUpdate({
      target: [storespecialhours.specialDateID, storespecialhours.day],
      set: { dayOpen: openingHours.dayOpen, dayClose: openingHours.dayClose },
    })
    .returning({
      specialDateID: storespecialhours.specialDateID,
      storeID: storespecialhours.storeID,
      day: storespecialhours.day,
      dayOpen: storespecialhours.dayOpen,
      dayClose: storespecialhours.dayClose,
    })

  const [storeHours] = await db
    .insert(storespecialhours)
    .values(openingHours)
    .onConflictDoUpdate({
      target: [storespecialhours.specialDateID, storespecialhours.day],
      set: { dayOpen: openingHours.dayOpen, dayClose: openingHours.dayClose },
    })
    .returning({
      specialDateID: storespecialhours.specialDateID,
      storeID: storespecialhours.storeID,
      day: storespecialhours.day,
      dayOpen: storespecialhours.dayOpen,
      dayClose: storespecialhours.dayClose,
    })

My goal is to insert new data into the storespecialhours table. If there is a conflict with specialDateID or (storeID and Day) then do an update. Lots of rows can have conflicting dayOpen, dayClose or day attributes that conflict. How do I fix it? Currently I am getting: there is no unique or exclusion constraint matching the ON CONFLICT specification

2 Replies

kyoumei•5mo ago

Did you ever find a solution to this? I've run into a similar issue and found your post

Artamiel•5mo ago

Hi, newbie trying to help The error message that you get indicates that there's no unique constraint placed on the column on the database level Can you check your table structure and make sure that unique constraints index is placed on those columns ?

Gaming

Programming

onConflictDoUpdate error, there is no unique or exclusion constraint matching the ON CONFLICT