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

Hibernate 6.6+ lets HQL express upserts with 'insert ... on conflict do update'. The conflict target can optionally name a unique constraint ('on conflict on constraint <name>'). The legacy DB2 translator emulates the conflict clause through DB2 MERGE, which cannot target a constraint by name, so visitConflictClause rejects the query with IllegalQueryOperationException while the HQL is being translated to SQL.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DB2LegacySqlAstTranslator.java:403

	protected void visitInsertStatementOnly(InsertSelectStatement statement) {
		final boolean closeWrapper = renderReturningClause( statement );
		if ( statement.getConflictClause() == null || statement.getConflictClause().isDoNothing() ) {
			// Render plain insert statement and possibly run into unique constraint violation
			super.visitInsertStatementOnly( statement );
		}
		else {
			visitInsertStatementEmulateMerge( statement );
		}
		if ( closeWrapper ) {
			appendSql( ')' );
		}
	}

	@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 void renderDmlTargetTableExpression(NamedTableReference tableReference) {
		super.renderDmlTargetTableExpression( tableReference );
		if ( getClauseStack().getCurrent() != Clause.INSERT ) {
			renderTableReferenceIdentificationVariable( tableReference );
		}
	}

	@Override
	protected void renderFromClauseAfterUpdateSet(UpdateStatement statement) {
		renderFromClauseExcludingDmlTargetReference( statement );
	}

	protected boolean renderReturningClause(MutationStatement statement) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Drop the named target: change 'on conflict on constraint <name> do update set ...' to 'on conflict do update set ...' so Hibernate infers the conflict target from the table's unique key
2. If the table has several unique constraints, disambiguate with an arbitrating predicate on the update (where clause) instead of naming a constraint
3. Rewrite the statement as native SQL using DB2's MERGE via session.createNativeQuery(...) or a @NativeQuery
4. Branch per database: keep the constraint-name form only for dialects that support it (e.g. PostgreSQL) and use the target-less form for DB2

Example fix

// before (HQL)
insert into Customer (id, name) select c.id, c.name from OldCustomer c
  on conflict on constraint uk_customer_id do update set name = excluded.name

// after (HQL) - let Hibernate infer the conflict target
insert into Customer (id, name) select c.id, c.name from OldCustomer c
  on conflict do update set name = excluded.name

// before building the upsert, check dialect capability
Dialect dialect = sessionFactory.getJdbcServices().getDialect();
boolean supportsNamedConflictTarget = !(dialect instanceof org.hibernate.community.dialect.DB2LegacyDialect);
if (!supportsNamedConflictTarget) {
    // strip 'on constraint <name>' from the conflict clause
    hql = hql.replaceFirst("on constraint \\w+", "");
}

static boolean namedConflictTargetSafe(Dialect d) {
    return !(d instanceof org.hibernate.community.dialect.DB2LegacyDialect);
}

// IllegalQueryOperationException is thrown when the HQL is translated
try {
    Query<?> q = session.createQuery(hql);
} catch (org.hibernate.query.IllegalQueryOperationException e) {
    log.warn("dialect cannot render named conflict target, retrying target-less", e);
    q = session.createQuery(stripConstraintTarget(hql));
}

When it happens

Trigger: Executing an HQL insert-select whose conflict clause names a constraint, e.g. 'insert into Order ... select ... on conflict on constraint uq_key do update set ...' while the session uses DB2LegacyDialect. The plain 'on conflict do update' (no target) and the column-list target 'on conflict (col) do update' render fine; only the constraint-name form throws.

Common situations: Porting PostgreSQL-style upsert HQL to DB2; sharing one HQL string between a Postgres/H2 test database and a DB2 production database; upgrading Hibernate to 6.6+ and adopting the new upsert syntax in code that must also run on DB2.

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/e4b2054390dbfec4. Report an issue: GitHub.