[BUG]: onConflictDoUpdate error in casing

[vf-unordinary]

What version of drizzle-orm are you using?

0.26.3

What version of drizzle-kit are you using?

0.18.1

Describe the Bug

Hey all!

I've found a funny problem while using drizzle + postgres (Vercel postgres).

Having this schema:

export const Processings = pgTable('processings', {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    estimateHours: integer('estimateHours').notNull(),
    processDefinitionId: integer('processDefinitionId').references(() => ProcessDefinitions.id)
});

When trying to upsert a new Processing using

await db
  .insert(Processings)
  .values(form.data.processings)
  .onConflictDoUpdate({
    target: Processings.id,
    set: {
      name: sql`excluded.name`,
      processDefinitionId: sql`excluded.processDefinitionId`,
      estimateHours: sql`excluded.estimateHours`,
    },
  });

The query returns this error:

error: column excluded.processdefinitionid does not exist

and further in the error:

hint: 'Perhaps you meant to reference the column "excluded.processDefinitionId".'

The difference is in the casing of processDefinitionId.
I'm on typescript so I'm using intellisense suggestions.

Expected behavior

No response

Environment & setup

I'm using sveltekit right now
Vercel Postgres
Typescript

[dankochetov]

You need to add quotes around the column name, otherwise the DB will treat it as a lower-case name. It's a standard DB behavior.

[vf-unordinary]

Hello Dan!
Could you provide an example? I see no quotes apart from ticks.
Any help is appreciated

[mPyKen]

ran into the same issue. as @dankochetov says, it should work if you add quotes "" around the column names name, processDefinitionId, estimateHours as below

set: {
  name: sql`excluded."name"`,
  processDefinitionId: sql`excluded."processDefinitionId"`,
  estimateHours: sql`excluded."estimateHours"`,
},

not sure if there is a cleaner way, but you could make it override all columns without explicitly stating them each like this (you might need to filter keys by the actual table columns to avoid sql injection)

set: {
  ...Object.fromEntries(
    Object.keys(form.data.processings[0] ?? {}).map(x => [x, sql.raw(`excluded."${x}"`)])
  ),
  // optionally override specific values
  // ...
},

[vf-unordinary]

That's a great idea, I'll use that. Thanks!

[GiovaniGuizzo]

Sorry to revive this thread again, but this bug happens on your guide for upset here: https://orm.drizzle.team/docs/guides/upsert
I suggest updating the documentation with the correct quote escaped column names.

[alanscodelog]

Also ran into this, made a pr for the docs: drizzle-team/drizzle-orm-docs#627