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 renders HQL/JPA INSERT ... ON CONFLICT (upsert) statements per dialect. HSQLDB has no native ON CONFLICT, so Hibernate emulates the upsert, and the emulation can only target conflict columns -- never a named constraint. When the SQM ConflictClause has both isDoUpdate() and a non-null constraintName (HQL 'on conflict on constraint <name> do update'), HSQLSqlAstTranslator.visitConflictClause rejects it with IllegalQueryOperationException while the SQL is being rendered, before anything reaches the database.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/HSQLSqlAstTranslator.java:93

		}
	}

	@Override
	protected void renderDerivedTableReference(DerivedTableReference tableReference) {
		if ( tableReference instanceof FunctionTableReference && tableReference.isLateral() ) {
			// No need for a lateral keyword for functions
			tableReference.accept( this );
		}
		else {
			super.renderDerivedTableReference( 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 void renderExpressionAsClauseItem(Expression expression) {
		expression.accept( this );
	}

	@Override
	public void visitBooleanExpressionPredicate(BooleanExpressionPredicate booleanExpressionPredicate) {
		final boolean isNegated = booleanExpressionPredicate.isNegated();
		if ( isNegated ) {
			appendSql( "not(" );
		}
		booleanExpressionPredicate.getExpression().accept( this );
		if ( isNegated ) {
			appendSql( CLOSE_PARENTHESIS );

View on GitHub (pinned to fad1729dce)

Solutions

1. Replace the constraint-name conflict target with a column list: 'on conflict (id) do update set ...' -- the HSQLDB emulation supports column-based targets
2. For HSQLDB runs, drop the conflict clause entirely and catch the constraint violation (SQLIntegrityConstraintViolationException) on the plain insert
3. Guard the HQL by dialect: only use 'on conflict on constraint' on dialects whose translators render it (e.g. PostgreSQL family)
4. If the exact upsert semantics are required on HSQLDB, use a native MERGE INTO query

Example fix

// before
em.createQuery("insert into Person p (p.id,p.name) values (:id,:n)"
    + " on conflict on constraint uk_person_name do update set p.name = excluded.name");

// after (column-based conflict target works on HSQLDB)
em.createQuery("insert into Person p (p.id,p.name) values (:id,:n)"
    + " on conflict (id) do update set p.name = excluded.name");

// Before building upsert HQL, check the dialect can target a named constraint
static boolean supportsConstraintNameConflictTarget(Dialect dialect) {
    // PostgreSQL-family translators render "on conflict on constraint";
    // HSQLDB does not -- extend this allow-list as you add dialects
    return dialect instanceof PostgreSQLDialect;
}

String hql = supportsConstraintNameConflictTarget(dialect)
    ? "... on conflict on constraint uk_x do update ..."
    : "... on conflict (id) do update ...";

try {
    em.createQuery(hql).executeUpdate();
} catch (IllegalQueryOperationException e) {
    // translation-time rejection: rewrite the conflict clause with a column target and retry
    log.warn("Constraint-name conflict target unsupported on {}", dialect.getClass().getSimpleName());
    throw new UnsupportedOperationException("Rewrite upsert with column conflict target", e);
}

When it happens

Trigger: Creating/executing HQL like 'insert into Person (id,name) values (:id,:n) on conflict on constraint uk_person_name do update set name = excluded.name' (or building the same via SqmInsertSelectStatement.getConflictClause().conflictOnConstraint("uk_...")) while the SessionFactory runs on HSQLDialect. The throw happens at query translation time (createQuery/executeUpdate), not at DB execution time.

Common situations: Running PostgreSQL-targeted upsert HQL (PostgreSQL accepts 'on constraint <name>') against in-memory HSQLDB tests; sharing one test-suite/persistence XML across dialects; adopting the HQL 'on conflict' clause added in Hibernate 6.6+ and assuming the constraint-name form is portable.

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