drizzle-team / drizzle-team/drizzle-orm

[BUG]: buildUpdateSet asymmetric with buildInsertQuery — does not filter generatedAlwaysAs columns from ON CONFLICT DO UPDATE SET (PG)

Open
#5,743 2 comments 0 reactions 0 assignees View on GitHub
bug/cant-reproduce
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

## Summary

In `drizzle-orm/src/pg-core/dialect.ts`, `buildInsertQuery` correctly filters
generated columns out of the INSERT column/VALUES list via
`!col.shouldDisableInsert()`, but `buildUpdateSet` (used for the
`onConflictDoUpdate` SET clause) does **not** apply the same filter.

As a result, when an `insert(...).onConflictDoUpdate({ set: {...} })` triggers
the UPDATE path on a table that contains a `generatedAlwaysAs` column,
Postgres rejects the statement with:

`ERROR: column "..." can only be updated to DEFAULT`
(or, on older PG versions: `cannot insert non-DEFAULT value into column "..."`)

INSERT works, UPSERT fails. The two code paths are asymmetric.

## Minimal reproduction (Postgres >= 12)

```ts
import { sql } from 'drizzle-orm';
import { pgTable, serial, integer } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const products = pgTable('products', {
id: serial('id').primaryKey(),
length_mm: integer('length_mm'),
width_mm: integer('width_mm'),
sum_dims: integer('sum_dims')
.generatedAlwaysAs(sql`length_mm + width_mm`)
.notNull(),
});

const db = drizzle(new Pool({ /* ... */ }));

// (1) Plain insert: WORKS (sum_dims correctly filtered out of column list)
await db.insert(products).values({ id: 1, length_mm: 1, width_mm: 2 });

// (2) Upsert: FAILS at the UPDATE branch
await db
.insert(products)
.values({ id: 1, length_mm: 10, width_mm: 20 })
.onConflictDoUpdate({
target: products.id,
set: { length_mm: 10, width_mm: 20 },
});
// PostgresError: column "sum_dims" can only be updated to DEFAULT
```

## Expected behaviour

`buildUpdateSet` should skip any column whose `shouldDisableInsert()` returns
true, mirroring `buildInsertQuery`. A generated-always column can never
appear in a SET clause as anything other than `DEFAULT`.

## Source-code references (upstream main HEAD)

- `drizzle-orm/src/pg-core/dialect.ts` L95-109 — `buildUpdateSet`:
```ts
const columnNames = Object.keys(tableColumns).filter((colName) =>
set[colName] !== undefined || tableColumns[colName]?.onUpdateFn !== undefined
);
```
No `shouldDisableInsert()` check.

- `drizzle-orm/src/pg-core/dialect.ts` L411 — `buildInsertQuery`:
```ts
const colEntries: [string, PgColumn][] = Object.entries(columns)
.filter(([_, col]) => !col.shouldDisableInsert());
```

- `drizzle-orm/src/column.ts` — `shouldDisableInsert`:
```ts
shouldDisableInsert(): boolean {
return this.config.generated !== undefined
&& this.config.generated.type !== 'byDefault';
}
```

## Suggested fix

In `buildUpdateSet` (pg-core, and mirrored in mysql-core / sqlite-core),
filter the column list with `shouldDisableInsert()`:

```ts
const columnNames = Object.keys(tableColumns).filter((colName) => {
const col = tableColumns[colName];
if (col?.shouldDisableInsert()) return false;
return set[colName] !== undefined || col?.onUpdateFn !== undefined;
});
```

Happy to open a PR.

## Postgres docs

> A generated column cannot be written to directly. In an INSERT or UPDATE
> command, a value cannot be specified for a generated column, but the
> keyword DEFAULT may be specified.

https://www.postgresql.org/docs/current/ddl-generated-columns.html

## Existing related issues (none cover this case)

- #3511 — generated columns in INSERT only (closed)
- #3024 — `excluded.*` casing (closed)
- #3730 — SQLite custom-type mapper (closed)
- #4929 — generated-column index recreation on DDL (different layer)
- #3608 — INSERT...SELECT column ordering (different layer)

## Environment

- drizzle-orm: latest `main` HEAD as of 2026-05-11
- Postgres: 16 (also reproduces on 14, 15)
- Driver: `pg` / `postgres.js` (dialect-level bug, driver-agnostic)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.