{"record":{"id":"0d7c6b6eeae5adfe","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint-0d7c6b","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/H2LegacySqlAstTranslator.java","lineNumber":179,"sourceCode":"\t\t}\n\t\telse {\n\t\t\tsuper.visitUpdateStatementOnly( statement );\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 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\tpublic void visitCteContainer(CteContainer cteContainer) {\n\t\t// H2 has various bugs in different versions that make it impossible to use CTEs with parameters reliably\n\t\twithParameterRenderingMode(\n\t\t\t\tSqlAstNodeRenderingMode.INLINE_PARAMETERS,\n\t\t\t\t() -> super.visitCteContainer( cteContainer )\n\t\t);\n\t}\n\n\t@Override\n\tprotected boolean needsCteInlining() {\n\t\t// CTEs in H2 are just so buggy, that we can't reliably use them\n\t\treturn true;\n\t}","sourceCodeStart":161,"sourceCodeEnd":197,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/H2LegacySqlAstTranslator.java#L161-L197","documentation":"Hibernate 6.6+ HQL upserts use 'insert ... on conflict do update', optionally with a named constraint target ('on conflict on constraint <name>'). H2LegacySqlAstTranslator.visitConflictClause throws IllegalQueryOperationException for that named-constraint form because the H2 conflict rendering cannot bind the upsert to a specific constraint name.","triggerScenarios":"An HQL insert with 'on conflict on constraint <constraintName> do update set ...' running under the H2 legacy dialect (H2LegacyDialect / H2LegacySqlAstTranslator). The target-less form and the column-list target 'on conflict (cols) do update' do not hit this throw.","commonSituations":"H2 test databases pinned to the legacy dialect after a Hibernate upgrade; test fixtures copied from PostgreSQL production queries that name constraints; multi-database test matrices where only the H2-legacy member fails.","solutions":["Remove the constraint name: 'on conflict do update set ...' - Hibernate infers the conflict target from the unique key","If disambiguation is needed, use the column-list target 'on conflict (col1, col2) do update'","Move the statement to a native H2 MERGE ... KEY query","Migrate the test database off the legacy dialect (H2 2.x with the current H2Dialect), which renders conflict clauses natively"],"exampleFix":"// before (HQL, throws on H2 legacy dialect)\ninsert into Person (id, name) select p.id, p.name from StagePerson p\n  on conflict on constraint uk_person_id do update set name = excluded.name\n\n// after (target-less conflict clause)\ninsert into Person (id, name) select p.id, p.name from StagePerson p\n  on conflict do update set name = excluded.name","handlingStrategy":"validation","validationCode":"Dialect d = sessionFactory.getJdbcServices().getDialect();\nif (d instanceof org.hibernate.community.dialect.H2LegacyDialect && hql.contains(\"on conflict on constraint\")) {\n    hql = hql.replaceFirst(\"on constraint \\\\w+\", \"\");\n}","typeGuard":"static boolean namedConflictTargetSafe(Dialect d) {\n    return !(d instanceof org.hibernate.community.dialect.H2LegacyDialect);\n}","tryCatchPattern":"try {\n    q = session.createQuery(hql);\n} catch (org.hibernate.query.IllegalQueryOperationException e) {\n    if (e.getMessage().contains(\"constraint name\")) {\n        q = session.createQuery(hql.replaceFirst(\"on constraint \\\\w+\", \"\"));\n    } else throw e;\n}","preventionTips":["Write upserts without a named constraint target unless a specific dialect requires it","Prefer the column-list target 'on conflict (cols)' over 'on constraint <name>' for portability","Upgrade test H2 databases off the legacy dialect to exercise modern conflict-clause rendering"],"tags":["hibernate","h2","legacy-dialect","hql","upsert","on-conflict"],"backgroundTag":"upsert-conflict-target-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}