{"record":{"id":"5ac57bea792b59d9","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint-5ac57b","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-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacySqlAstTranslator.java","lineNumber":124,"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 visitSqlSelection(SqlSelection sqlSelection) {\n\t\tif ( getCurrentCteStatement() != null ) {\n\t\t\tif ( getCurrentCteStatement().getMaterialization() == CteMaterialization.MATERIALIZED ) {\n\t\t\t\tappendSql( \"/*+ materialize */ \" );\n\t\t\t}\n\t\t}\n\t\tsuper.visitSqlSelection( sqlSelection );\n\t}","sourceCodeStart":106,"sourceCodeEnd":142,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacySqlAstTranslator.java#L106-L142","documentation":"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.","triggerScenarios":"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.","commonSituations":"Porting PostgreSQL-native upsert HQL to Oracle; choosing constraint-name targets because they were convenient on Postgres; shared repository code used against multiple databases.","solutions":["Use a column list as the conflict target: 'on conflict (id) do update set ...'","Use 'on conflict do nothing', which needs no target","Perform the upsert with a native MERGE statement on Oracle","Scan dynamic HQL for 'on conflict on constraint' before executing it against this dialect"],"exampleFix":"-- before (HQL)\ninsert into Customer (id, email)\nselect i.id, i.email from ImportRow i\non conflict on constraint customer_pkey do update set email = excluded.email\n\n-- after (HQL)\ninsert into Customer (id, email)\nselect i.id, i.email from ImportRow i\non conflict (id) do update set email = excluded.email","handlingStrategy":"validation","validationCode":"boolean usesConstraintTarget(String hql, Dialect dialect) {\n    return hql != null\n        && hql.toLowerCase( Locale.ROOT ).contains( \"on conflict on constraint\" )\n        && dialect instanceof OracleLegacyDialect;\n}\n\nif ( usesConstraintTarget( hql, dialect ) ) {\n    hql = hql.replaceAll( \"(?i)on constraint \\\\S+\", \"(id)\" ); // column-list target\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createMutationQuery( hql ).executeUpdate();\n}\ncatch ( IllegalQueryOperationException e ) {\n    // rewrite 'on conflict on constraint X do update' to 'on conflict (cols) do update',\n    // or run a native MERGE instead\n    throw e;\n}","preventionTips":["Always use explicit column conflict targets in portable HQL upserts","Keep dialect-specific upserts in native SQL (MERGE on Oracle)","Add a CI smoke test that executes every upsert query against the target dialect"],"tags":["oracle","hql","upsert","insert-on-conflict","hibernate6"],"backgroundTag":"insert-on-conflict-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}