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),
  }),
)


  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
Was this page helpful?