{"record":{"id":"e4b2054390dbfec4","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint","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/DB2LegacySqlAstTranslator.java","lineNumber":403,"sourceCode":"\tprotected void visitInsertStatementOnly(InsertSelectStatement statement) {\n\t\tfinal boolean closeWrapper = renderReturningClause( statement );\n\t\tif ( statement.getConflictClause() == null || statement.getConflictClause().isDoNothing() ) {\n\t\t\t// Render plain insert statement and possibly run into unique constraint violation\n\t\t\tsuper.visitInsertStatementOnly( statement );\n\t\t}\n\t\telse {\n\t\t\tvisitInsertStatementEmulateMerge( statement );\n\t\t}\n\t\tif ( closeWrapper ) {\n\t\t\tappendSql( ')' );\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 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 renderFromClauseAfterUpdateSet(UpdateStatement statement) {\n\t\trenderFromClauseExcludingDmlTargetReference( statement );\n\t}\n\n\tprotected boolean renderReturningClause(MutationStatement statement) {","sourceCodeStart":385,"sourceCodeEnd":421,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DB2LegacySqlAstTranslator.java#L385-L421","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["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","If the table has several unique constraints, disambiguate with an arbitrating predicate on the update (where clause) instead of naming a constraint","Rewrite the statement as native SQL using DB2's MERGE via session.createNativeQuery(...) or a @NativeQuery","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"],"exampleFix":"// before (HQL)\ninsert into Customer (id, name) select c.id, c.name from OldCustomer c\n  on conflict on constraint uk_customer_id do update set name = excluded.name\n\n// after (HQL) - let Hibernate infer the conflict target\ninsert into Customer (id, name) select c.id, c.name from OldCustomer c\n  on conflict do update set name = excluded.name","handlingStrategy":"validation","validationCode":"// before building the upsert, check dialect capability\nDialect dialect = sessionFactory.getJdbcServices().getDialect();\nboolean supportsNamedConflictTarget = !(dialect instanceof org.hibernate.community.dialect.DB2LegacyDialect);\nif (!supportsNamedConflictTarget) {\n    // strip 'on constraint <name>' from the conflict clause\n    hql = hql.replaceFirst(\"on constraint \\\\w+\", \"\");\n}","typeGuard":"static boolean namedConflictTargetSafe(Dialect d) {\n    return !(d instanceof org.hibernate.community.dialect.DB2LegacyDialect);\n}","tryCatchPattern":"// IllegalQueryOperationException is thrown when the HQL is translated\ntry {\n    Query<?> q = session.createQuery(hql);\n} catch (org.hibernate.query.IllegalQueryOperationException e) {\n    log.warn(\"dialect cannot render named conflict target, retrying target-less\", e);\n    q = session.createQuery(stripConstraintTarget(hql));\n}","preventionTips":["Standardize on the target-less 'on conflict do update' form in shared HQL - it renders on every dialect","Keep a per-dialect matrix of upsert syntax in the test suite so unsupported forms fail in CI, not production","When porting PostgreSQL upserts, strip 'on constraint <name>' as a porting checklist item for DB2/HANA/H2"],"tags":["hibernate","db2","hql","upsert","on-conflict","insert"],"backgroundTag":"upsert-conflict-target-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T14:17:55.899Z"}