Postgres Support onConflictDoUpdate with Arrays of values

[JonoPrest]

Hi there,

We would love to be able to perform an "Upsert" on an array of values. In the docs all the demos are for single values passed in.

Current docs:

await db.insert(users)
  .values({ id: 1, name: 'Dan' })
  .onConflictDoUpdate({ target: users.id, set: { name: 'John' } });

await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onConflictDoNothing();

await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onConflictDoNothing({ target: users.id });

await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onConflictDoUpdate({
    target: users.id,
    set: { name: 'John1' },
    where: sql`${users.createdAt} > '2023-01-01'::date`,
  });

It would be great to be able to do something like this:

await db.insert(users)
.values([{ id: 1, name: 'Dan' }, { id: 2, name: 'John' }])
.onConflictDoUpdate((user) => { target: users.id, set: { name: user.name } });

That way we can set values based on the specific user that caused the conflict. I'm not sure if there is a way to handle this use case currently but a callback might be the easiest way to implement something like this.

[pyk]

@JonoPrest you can do something like this:

await db.insert(users)
.values([{ id: 1, name: 'Dan' }, { id: 2, name: 'John' }])
.onConflictDoUpdate((user) => { target: users.id, set: { name: sql`EXCLUDED.name` } });

EXCLUDED will be automatically replaced with conflicted row

[johnrackles]

Should that code example not be more like this:

await db.insert(users)
.values([{ id: 1, name: 'Dan' }, { id: 2, name: 'John' }])
.onConflictDoUpdate({ target: users.id, set: { name: sql`EXCLUDED.name` });

I don't think onConflictDoUpdate takes a function as argument?

[corscheid]

UPDATE: nevermind, I was using the wrong sql import. There is no type issue, there's just a db issue (there is no unique or exclusion constraint matching the ON CONFLICT specification)

Disregard:

I just tried this, and it doesn't work because of incompatible types:

Type 'Promise<QueryResult<QueryResultRow>>' is not assignable to type 'string | SQL<unknown> | undefined'.

the problem is this part:

set: { name: sql`EXCLUDED.name` }

I really still kinda want the thing JonoPrest opened this discussion with. But seeing as this or something like it does not currently exist, does this mean that I need to run the query in a loop over the array? I don't love that, but I'll take it, if it's the only way, for now.

[aleclarson]

Hello all. @pyk's example is basically how I've implemented db.query.xyz.upsert(…) in my drizzle-plus package. Please give it a look. Note that it only works with drizzle-orm@beta (Drizzle v1.0+).