{"record":{"id":"3225d5f0c0ee086b","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint-3225d5","errorCode":null,"errorMessage":"Insert conflict 'do update' clause with constraint name is not supported","messagePattern":"Insert conflict 'do update' clause with constraint name is not supported","errorType":"exception","errorClass":"IllegalQueryOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/OracleSqlAstTranslator.java","lineNumber":128,"sourceCode":"\tprotected void renderMergeUpdateClause(List<Assignment> assignments, Predicate wherePredicate) {\n\t\tappendSql( \" then update\" );\n\t\trenderSetClause( assignments );\n\t\tvisitWhereClause( wherePredicate );\n\t}\n\n\t@Override\n\tprotected void renderDmlTargetTableExpression(NamedTableReference tableReference) {\n\t\tsuper.renderDmlTargetTableExpression( tableReference );\n\t\tif ( getClauseStack().getCurrent() != Clause.INSERT ) {\n\t\t\trenderTableReferenceIdentificationVariable( tableReference );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void visitConflictClause(ConflictClause conflictClause) {\n\t\tif ( conflictClause != null ) {\n\t\t\tif ( conflictClause.isDoUpdate() && conflictClause.getConstraintName() != null ) {\n\t\t\t\tthrow new IllegalQueryOperationException( \"Insert conflict 'do update' clause with constraint name is not supported\" );\n\t\t\t}\n\t\t}\n\t}\n\n\t@Override\n\tprotected boolean needsRecursiveKeywordInWithClause() {\n\t\treturn false;\n\t}\n\n\t@Override\n\tpublic void visitInArrayPredicate(InArrayPredicate inArrayPredicate) {\n\t\t// column in (select column_value from(?) )\n\t\tinArrayPredicate.getTestExpression().accept( this );\n\t\tappendSql( \" in (select column_value from table(\" );\n\t\tinArrayPredicate.getArrayParameter().accept( this );\n\t\tappendSql( \"))\" );\n\t}\n","sourceCodeStart":110,"sourceCodeEnd":146,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/OracleSqlAstTranslator.java#L110-L146","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Use a column-based conflict target: 'on conflict (id) do update set ...' -- Oracle translation supports it","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","For Oracle-specific upserts, use a native MERGE INTO query","Catch IllegalQueryOperationException and branch to a dialect-specific statement when one code path must serve both PostgreSQL and Oracle"],"exampleFix":"// before\nString hql = \"insert into OrderStage o (o.ref,o.amount) values (:r,:a)\"\n    + \" on conflict on constraint uk_orderstage_ref do update set o.amount = excluded.amount\";\n\n// after\nString hql = \"insert into OrderStage o (o.ref,o.amount) values (:r,:a)\"\n    + \" on conflict (ref) do update set o.amount = excluded.amount\";","handlingStrategy":"validation","validationCode":"static boolean supportsConstraintNameConflictTarget(Dialect dialect) {\n    // Oracle's translator rejects the constraint-name form\n    return dialect instanceof PostgreSQLDialect && !(dialect instanceof SpannerPostgreSQLDialect);\n}\n\nString conflict = supportsConstraintNameConflictTarget(dialect)\n    ? \" on conflict on constraint \" + constraintName + \" do update set ...\"\n    : \" on conflict (\" + constraintColumns + \") do update set ...\";","typeGuard":null,"tryCatchPattern":"try {\n    query = em.createQuery(hql);\n} catch (IllegalQueryOperationException e) {\n    // strip \"on constraint <name>\" and rebuild with the constraint's column list\n    throw new IllegalArgumentException(\"Use a column-based conflict target on Oracle\", e);\n}","preventionTips":["Prefer column-list conflict targets in all shared HQL","Encode each unique constraint's columns in metadata so you can always fall back to a column target","Cover Oracle in the CI dialect matrix when upsert HQL changes"],"tags":["hibernate","oracle","hql","upsert","on-conflict","insert","query-translation"],"backgroundTag":"upsert-on-conflict-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}