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:
And I'm trying to do an upsert like this:
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
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'
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 keyYeah here's the full error:
Let me test without the
ISOString
, thank you!!You are welcome! Write to me if the problem persists
@solo Unfortunately I'm still getting the same error. This is what I changed my code to:
This is the error again:
@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?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
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?
Can you show how you are doing it? If you ignore the type errors, does the query works?
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).
Thank you for your help @solo @Angelelz !!!nice, you are welcome!
@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 conflictProperty '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)
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?
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
I believe the query is valid without the target, so I guess this is a bug
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...
Did you verify that the target is actually not required? I'm working from memory here
im going off of what you said earlier in the thread. i would assume the target would be reuiqred
I might be wrong, a quick check to the Postgres docs should suffice but I'm on mobile
good to close ticket i guess
behavior is correct
I was wrong, I kinda remember that, maybe it was mysql