Confused about onConflictDoUpdate when having a constraint

I have a table that looks like

export const weeklyFinalRankings = pgTable(
  'weekly_final_rankings',
  {
    id: serial('id').primaryKey(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year').notNull(),
    rankings: jsonb('rankings').notNull(),
  },
  (table) => ({
    isUniqueVote: unique().on(table.year, table.week),
  }),
)

export const weeklyFinalRankings = pgTable(
  'weekly_final_rankings',
  {
    id: serial('id').primaryKey(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year').notNull(),
    rankings: jsonb('rankings').notNull(),
  },
  (table) => ({
    isUniqueVote: unique().on(table.year, table.week),
  }),
)

and after going through some docs, I was doing an insert like so

await db
  .insert(weeklyFinalRankings)
  .values({
    division,
    year: 2024,
    week: 0,
    rankings: rankedTeams,
  })
  .onConflictDoUpdate({
    target: weeklyFinalRankings.week,
    set: { rankings: rankedTeams },
  })

await db
  .insert(weeklyFinalRankings)
  .values({
    division,
    year: 2024,
    week: 0,
    rankings: rankedTeams,
  })
  .onConflictDoUpdate({
    target: weeklyFinalRankings.week,
    set: { rankings: rankedTeams },
  })

However, I get PostgresError: there is no unique or exclusion constraint matching the ON CONFLICT specification What am I doing incorrect here?

3 Replies

rafar•5mo ago

i also been having quite a few problems with adding constraints - in my case its slightly more complicated with joins - but in order to understand if my troubleshooting is going on the right path might I ask you what happens if you do:

 target: [weeklyFinalRankings.week, weeklyFinalRankings.year]

 target: [weeklyFinalRankings.week, weeklyFinalRankings.year]

because then the target would indeed match the constraint in your schema which takes into account also the year column. let me know if it helps 🙂

jsingleton37OP•5mo ago

Ah sweet! That fixes it! Also makes me realize I need to update the unique to be on division, week, and year 😅

rafar•5mo ago

that’s great, glad to be of help!

Gaming

Programming

Confused about onConflictDoUpdate when having a constraint