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

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

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?

21 Replies

Mykhailo•12mo ago

Hello, @SKUZZIE! I think there is a problem with date: date.toISOString(),. You declared date column as date("date").notNull(), which format is (year-month-day), but when you insert values you pass a string that represents date in this format YYYY-MM-DDTHH:mm:ss.sssZ (with timezone). It might be that in database you have one value (for example 2023-12-14) and you try to insert another value with timezone, so date is not the same. So, in this case you get duplicate key value violates unique constraint error not for your target [dailyStats.ad_id, dailyStats.date], but just for ad_id, because it is primary key in your dailyStats table and it automatically has unique index. I tried your code with mock data and it works: I only add userCount field, which is just integer. In db I already have row with ad_id = 'testId1' and date = '2023-12-14'

const date = '2023-12-14';
const dailyStatsInsert = {
  userCount: 20,
};

await db
    .insert(dailyStats)
    .values({
      ad_id: 'testId1',
      date: date,
      ...dailyStatsInsert,
    })
    .onConflictDoUpdate({
      target: [dailyStats.ad_id, dailyStats.date],
      set: dailyStatsInsert,
    });

const date = '2023-12-14';
const dailyStatsInsert = {
  userCount: 20,
};

await db
    .insert(dailyStats)
    .values({
      ad_id: 'testId1',
      date: date,
      ...dailyStatsInsert,
    })
    .onConflictDoUpdate({
      target: [dailyStats.ad_id, dailyStats.date],
      set: dailyStatsInsert,
    });

and it will update userCount value to 20 Could you send the name of constraint from your unique constraint error? Just to be sure, that it is related to primary key

SKUZZIEOP•12mo ago

Yeah here's the full error:

NeonDbError: db error: ERROR: duplicate key value violates unique constraint "daily_stats_pkey"
DETAIL: Key (ad_id)=(MGIkhCKqgCE0Ga1J) already exists.

NeonDbError: db error: ERROR: duplicate key value violates unique constraint "daily_stats_pkey"
DETAIL: Key (ad_id)=(MGIkhCKqgCE0Ga1J) already exists.

Let me test without the ISOString, thank you!!

Mykhailo•12mo ago

You are welcome! Write to me if the problem persists

SKUZZIEOP•12mo ago

@solo Unfortunately I'm still getting the same error. This is what I changed my code to:

const date = dayjs().format("YYYY-MM-DD");
...
await db
        .insert(dailyStats)
        .values({
          ad_id: dbAd.id,
          date: date,
          ...dailyStatsInsert,
        })
        .onConflictDoUpdate({
          target: [dailyStats.ad_id, dailyStats.date],
          set: dailyStatsInsert,
        });

const date = dayjs().format("YYYY-MM-DD");
...
await db
        .insert(dailyStats)
        .values({
          ad_id: dbAd.id,
          date: date,
          ...dailyStatsInsert,
        })
        .onConflictDoUpdate({
          target: [dailyStats.ad_id, dailyStats.date],
          set: dailyStatsInsert,
        });

This is the error again:

NeonDbError: db error: ERROR: duplicate key value violates unique constraint "daily_stats_pkey"
DETAIL: Key (ad_id)=(QP1VlfoJo4QQ_XTL) already exists.

NeonDbError: db error: ERROR: duplicate key value violates unique constraint "daily_stats_pkey"
DETAIL: Key (ad_id)=(QP1VlfoJo4QQ_XTL) already exists.

Mykhailo•12mo ago

@SKUZZIE it seems that error is again related to your ad_id primary key. I tried again and get the same error only if my date value in insert query and date value in db are different, but ad_id is the same. Could you tell the version of drizzle-orm that you are using and are you sure that date values in db and insert query are the same?

Angelelz•12mo ago

Can you try passing only the ad_id column to target? Another option is to pass it the index name Lastly, you should be able to just omit the target and postgres should be able to infer the conflict

SKUZZIEOP•12mo ago

I was going to try the last two ideas you had, but doing either throws type errors. Are you sure doing that is supported in Drizzle?

Angelelz•12mo ago

Can you show how you are doing it? If you ignore the type errors, does the query works?

SKUZZIEOP•12mo ago

Oh my gosh I think I figured it out. It's because I had ad_id set as the primary key, but I really want both ad_id and date to be the primary key. The upsert was working, it was erroring when I was creating another record with the same primary key (as it's supposed to). facepalm

Thank you for your help @solo @Angelelz !!!

Mykhailo•12mo ago

nice, you are welcome!

jakeleventhal•11mo ago

@Angelelz what is the purpose of the target then if postgres can infer the conflicts anyway

Lastly, you should be able to just omit the target and postgres should be able to infer the conflict

Property 'target' is missing in type '{ set: { title: string; }; }' but required in type 'PgInsertOnConflictDoUpdateConfig<PgInsertBase<PgTableWithColumns<{ name: "InventoryItem"; schema: undefined; columns: { archived: PgColumn<{ name: "archived"; tableName: "InventoryItem"; dataType: "boolean"; columnType: "PgBoolean"; data: boolean; driverParam: boolean; notNull: true; hasDefault: true; enumValues: un...'.ts(2345)

Angelelz•11mo ago

You can read about it in the Postgres docs. As for the type error you're getting, does the query work if you ignore it?

jakeleventhal•11mo ago

no [0] TypeError: Cannot read properties of undefined (reading 'name') [0] at QueryPromise.onConflictDoUpdate (/Users/jakeleventhal/Code/rip-technologies/nodemodules/.pnpm/[email protected]@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/src/pg-core/query-builders/insert.ts:275:44) [0] at /Users/jakeleventhal/Code/rip-technologies/apps/ecominate/api/src/api.ts:119:10 [0] at Generator.next (<anonymous>) [0] at fulfilled (/Users/jakeleventhal/Code/rip-technologies/apps/ecominate/api/src/api.ts:28:58) [0] at processTicksAndRejections (node:internal/process/task_queues:95:5

Angelelz•11mo ago

I believe the query is valid without the target, so I guess this is a bug

jakeleventhal•11mo ago

https://github.com/drizzle-team/drizzle-orm/issues/1762

GitHub

[BUG]: Upsert operation requires target when it shouldn't · Issue #...

What version of drizzle-orm are you using? 0.29.2 What version of drizzle-kit are you using? 0.20.9 Describe the Bug Target should be able to be inferred in upsert operations. Full context in threa...

Angelelz•11mo ago

Did you verify that the target is actually not required? I'm working from memory here

jakeleventhal•11mo ago

im going off of what you said earlier in the thread. i would assume the target would be reuiqred

Angelelz•11mo ago

I might be wrong, a quick check to the Postgres docs should suffice but I'm on mobile

jakeleventhal•11mo ago

https://arc.net/l/quote/pqstvqbb

For ON CONFLICT DO UPDATE, a conflict_target must be provided.

Quote from “PostgreSQL: Documentation: 16: INSERT”

jakeleventhal•11mo ago

good to close ticket i guess behavior is correct

Angelelz•11mo ago

I was wrong, I kinda remember that, maybe it was mysql

Gaming

Programming

Upsert with multi-column unique index?