drizzle-team/drizzle-orm · error · Error

You cannot use both "where" and "targetWhere"/"setWhere" at

Error message

You cannot use both "where" and "targetWhere"/"setWhere" at the same time - "where" is deprecated, use "targetWhere" or "setWhere" instead.

What it means

PgInsert.onConflictDoUpdate (insert.ts:372) forbids combining the legacy `where` option with the newer `targetWhere`/`setWhere`. The `where` key was deprecated because it was ambiguous; `targetWhere` qualifies the conflict target (the partial index predicate region) and `setWhere` qualifies the UPDATE. Mixing them would produce ambiguous SQL, so the library rejects the combination explicitly.

Source

Thrown at drizzle-orm/src/pg-core/query-builders/insert.ts:373

	 *     target: cars.id,
	 *     set: { brand: 'Porsche' }
	 *   });
	 *
	 * // Upsert with 'where' clause
	 * await db.insert(cars)
	 *   .values({ id: 1, brand: 'BMW' })
	 *   .onConflictDoUpdate({
	 *     target: cars.id,
	 *     set: { brand: 'newBMW' },
	 *     targetWhere: sql`${cars.createdAt} > '2023-01-01'::date`,
	 *   });
	 * ```
	 */
	onConflictDoUpdate(
		config: PgInsertOnConflictDoUpdateConfig<this>,
	): PgInsertWithout<this, TDynamic, 'onConflictDoNothing' | 'onConflictDoUpdate'> {
		if (config.where && (config.targetWhere || config.setWhere)) {
			throw new Error(
				'You cannot use both "where" and "targetWhere"/"setWhere" at the same time - "where" is deprecated, use "targetWhere" or "setWhere" instead.',
			);
		}
		const whereSql = config.where ? sql` where ${config.where}` : undefined;
		const targetWhereSql = config.targetWhere ? sql` where ${config.targetWhere}` : undefined;
		const setWhereSql = config.setWhere ? sql` where ${config.setWhere}` : undefined;
		const setSql = this.dialect.buildUpdateSet(this.config.table, mapUpdateSet(this.config.table, config.set));
		let targetColumn = '';
		targetColumn = Array.isArray(config.target)
			? config.target.map((it) => this.dialect.escapeName(this.dialect.casing.getColumnCasing(it))).join(',')
			: this.dialect.escapeName(this.dialect.casing.getColumnCasing(config.target));
		this.config.onConflict = sql`(${
			sql.raw(targetColumn)
		})${targetWhereSql} do update set ${setSql}${whereSql}${setWhereSql}`;
		return this as any;
	}

	/** @internal */

View on GitHub (pinned to b7862528fd)

Solutions

1. Remove the deprecated `where` and use `targetWhere` (to filter the conflict target) and/or `setWhere` (to filter the UPDATE).
2. If `where` was meant to gate the UPDATE, move it to `setWhere`.
3. If `where` was meant to qualify the index target, move it to `targetWhere`.
4. Audit all onConflictDoUpdate call sites after upgrading drizzle-orm to remove residual `where` keys.

Example fix

// before
db.insert(t).values(r).onConflictDoUpdate({
  target: t.id, set: { v: 'x' },
  where: sql`t.v > 0`, targetWhere: sql`t.tenant = 1`, // conflict
});

// after
db.insert(t).values(r).onConflictDoUpdate({
  target: t.id, set: { v: 'x' },
  targetWhere: sql`t.tenant = 1`, setWhere: sql`t.v > 0`,
});

function normalizeConflict(cfg: any) {
  if (cfg.where && (cfg.targetWhere || cfg.setWhere)) {
    // migrate legacy where into setWhere (UPDATE predicate) by default
    cfg.setWhere = cfg.setWhere ?? cfg.where;
    delete cfg.where;
  }
  return cfg;
}
await db.insert(t).values(r).onConflictDoUpdate(normalizeConflict(conflictConfig));

function isLegacyWhere(cfg: unknown): cfg is { where: unknown } {
  return typeof cfg === 'object' && cfg !== null && 'where' in cfg;
}

When it happens

Trigger: Calling onConflictDoUpdate({ target, set, where, targetWhere }) or onConflictDoUpdate({ target, set, where, setWhere }) in the same config object — typically during a migration from the old `where` API to the new split predicates.

Common situations: Upgrading drizzle-orm and copy-pasting a partial-index upsert example that uses targetWhere while still holding an old `where`; refactoring an upsert to support a partial unique index; merging code that inconsistently uses the two APIs.

Related errors

• You cannot use both "where" and "targetWhere"/"setWhere" at
• values() must be called with at least one value
• Insert select error: selected fields are not the same or are
• values() must be called with at least one value
• Insert select error: selected fields are not the same or are

AI-assisted analysis of drizzle-team/drizzle-orm@b7862528fd (2026-08-03). Data as JSON: /data/errors/e920ea8cc2c48222.json. Report an issue: GitHub.