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+ supports HQL upserts: 'insert into ... select ... on conflict ... do update'. The conflict target can be a column list or a constraint name, but Oracle cannot bind the DO UPDATE action to a named constraint the way PostgreSQL does; OracleLegacySqlAstTranslator.visitConflictClause() detects 'on conflict on constraint X do update' during SQL translation and aborts with IllegalQueryOperationException.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacySqlAstTranslator.java:124

	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 visitSqlSelection(SqlSelection sqlSelection) {
		if ( getCurrentCteStatement() != null ) {
			if ( getCurrentCteStatement().getMaterialization() == CteMaterialization.MATERIALIZED ) {
				appendSql( "/*+ materialize */ " );
			}
		}
		super.visitSqlSelection( sqlSelection );
	}

View on GitHub (pinned to fad1729dce)

Solutions

1. Use a column list as the conflict target: 'on conflict (id) do update set ...'
2. Use 'on conflict do nothing', which needs no target
3. Perform the upsert with a native MERGE statement on Oracle
4. Scan dynamic HQL for 'on conflict on constraint' before executing it against this dialect

Example fix

-- before (HQL)
insert into Customer (id, email)
select i.id, i.email from ImportRow i
on conflict on constraint customer_pkey do update set email = excluded.email

-- after (HQL)
insert into Customer (id, email)
select i.id, i.email from ImportRow i
on conflict (id) do update set email = excluded.email

boolean usesConstraintTarget(String hql, Dialect dialect) {
    return hql != null
        && hql.toLowerCase( Locale.ROOT ).contains( "on conflict on constraint" )
        && dialect instanceof OracleLegacyDialect;
}

if ( usesConstraintTarget( hql, dialect ) ) {
    hql = hql.replaceAll( "(?i)on constraint \\S+", "(id)" ); // column-list target
}

try {
    return session.createMutationQuery( hql ).executeUpdate();
}
catch ( IllegalQueryOperationException e ) {
    // rewrite 'on conflict on constraint X do update' to 'on conflict (cols) do update',
    // or run a native MERGE instead
    throw e;
}

When it happens

Trigger: Executing HQL like 'insert into Customer (id, email) select ... on conflict on constraint customer_pkey do update set email = excluded.email' on OracleLegacyDialect, or a Criteria insert-with-conflict built with a constraint-name conflict action.

Common situations: Porting PostgreSQL-native upsert HQL to Oracle; choosing constraint-name targets because they were convenient on Postgres; shared repository code used against multiple databases.

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