hibernate/hibernate-orm · error · IllegalQueryOperationException

Insert conflict 'do update' clause with constraint name is…

Error message

Insert conflict 'do update' clause with constraint name is not supported

What it means

Oracle gets its ON CONFLICT upsert rendered via emulation (MERGE-based), and like the other emulating translators, OracleSqlAstTranslator.visitConflictClause cannot map a conflict target given as a constraint name. If the statement's ConflictClause is a DO UPDATE variant with a non-null constraintName ('on conflict on constraint <name> do update'), translation aborts with IllegalQueryOperationException before any SQL is executed. Oracle's own native syntax has no 'ON CONFLICT ON CONSTRAINT' equivalent (that is PostgreSQL-specific), so the construct is rejected rather than silently mistranslated.

Solutions

  1. Use a column-based conflict target: 'on conflict (id) do update set ...' -- Oracle translation supports it
  2. If you truly need constraint-anchored behavior, keep only the PK/unique columns as the conflict target so the column list is equivalent to the constraint
  3. For Oracle-specific upserts, use a native MERGE INTO query
  4. Catch IllegalQueryOperationException and branch to a dialect-specific statement when one code path must serve both PostgreSQL and Oracle

Example fix

// before
String hql = "insert into OrderStage o (o.ref,o.amount) values (:r,:a)"
    + " on conflict on constraint uk_orderstage_ref do update set o.amount = excluded.amount";

// after
String hql = "insert into OrderStage o (o.ref,o.amount) values (:r,:a)"
    + " on conflict (ref) do update set o.amount = excluded.amount";
Defensive patterns

Strategy: validation

Validate before calling

static boolean supportsConstraintNameConflictTarget(Dialect dialect) {
    // Oracle's translator rejects the constraint-name form
    return dialect instanceof PostgreSQLDialect && !(dialect instanceof SpannerPostgreSQLDialect);
}

String conflict = supportsConstraintNameConflictTarget(dialect)
    ? " on conflict on constraint " + constraintName + " do update set ..."
    : " on conflict (" + constraintColumns + ") do update set ...";

Try / catch

try {
    query = em.createQuery(hql);
} catch (IllegalQueryOperationException e) {
    // strip "on constraint <name>" and rebuild with the constraint's column list
    throw new IllegalArgumentException("Use a column-based conflict target on Oracle", e);
}

Prevention

When it happens

Trigger: HQL 'insert into ... values/select ... on conflict on constraint <constraintName> do update set ...' executed with OracleDialect; or a programmatically built SqmConflictClause on which conflictOnConstraint("...") was called. Fails at query translation (createQuery/executeUpdate), not on the Oracle server.

Common situations: Porting an application from PostgreSQL (where 'on conflict on constraint pk_x do update' is valid) to Oracle; reusing cross-dialect test suites that include the constraint-name upsert form; adding the Hibernate 6.6+ HQL conflict clause by copying PostgreSQL documentation examples.

Related errors

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/3225d5f0c0ee086b. Report an issue: GitHub.

Appendix: source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/OracleSqlAstTranslator.java:128

	protected void renderMergeUpdateClause(List<Assignment> assignments, Predicate wherePredicate) {
		appendSql( " then update" );
		renderSetClause( assignments );
		visitWhereClause( wherePredicate );
	}

	@Override
	protected void renderDmlTargetTableExpression(NamedTableReference tableReference) {
		super.renderDmlTargetTableExpression( tableReference );
		if ( getClauseStack().getCurrent() != Clause.INSERT ) {
			renderTableReferenceIdentificationVariable( tableReference );
		}
	}

	@Override
	protected void visitConflictClause(ConflictClause conflictClause) {
		if ( conflictClause != null ) {
			if ( conflictClause.isDoUpdate() && conflictClause.getConstraintName() != null ) {
				throw new IllegalQueryOperationException( "Insert conflict 'do update' clause with constraint name is not supported" );
			}
		}
	}

	@Override
	protected boolean needsRecursiveKeywordInWithClause() {
		return false;
	}

	@Override
	public void visitInArrayPredicate(InArrayPredicate inArrayPredicate) {
		// column in (select column_value from(?) )
		inArrayPredicate.getTestExpression().accept( this );
		appendSql( " in (select column_value from table(" );
		inArrayPredicate.getArrayParameter().accept( this );
		appendSql( "))" );
	}

View on GitHub (pinned to fad1729dce)