hibernate/hibernate-orm · error · IllegalQueryOperationException

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

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.

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)

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";

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 {
    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);
}

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

• Insert conflict 'do update' clause with constraint name is n
• Insert conflict 'do update' clause with constraint name is n
• Insert conflict 'do update' clause with constraint name is n
• Insert conflict 'do update' clause with constraint name is n
• Insert conflict 'do update' clause with constraint name is n

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