{"record":{"id":"3e753454213c270a","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint-3e7534","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/HSQLLegacySqlAstTranslator.java","lineNumber":93,"sourceCode":"\t\t}\n\t}\n\n\t@Override\n\tprotected void renderDerivedTableReference(DerivedTableReference tableReference) {\n\t\tif ( tableReference instanceof FunctionTableReference && tableReference.isLateral() ) {\n\t\t\t// No need for a lateral keyword for functions\n\t\t\ttableReference.accept( this );\n\t\t}\n\t\telse {\n\t\t\tsuper.renderDerivedTableReference( 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 void renderExpressionAsClauseItem(Expression expression) {\n\t\texpression.accept( this );\n\t}\n\n\t@Override\n\tpublic void visitBooleanExpressionPredicate(BooleanExpressionPredicate booleanExpressionPredicate) {\n\t\tfinal boolean isNegated = booleanExpressionPredicate.isNegated();\n\t\tif ( isNegated ) {\n\t\t\tappendSql( \"not(\" );\n\t\t}\n\t\tbooleanExpressionPredicate.getExpression().accept( this );\n\t\tif ( isNegated ) {\n\t\t\tappendSql( CLOSE_PARENTHESIS );","sourceCodeStart":75,"sourceCodeEnd":111,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/HSQLLegacySqlAstTranslator.java#L75-L111","documentation":"HSQLLegacySqlAstTranslator.visitConflictClause() rejects HQL INSERT ... ON CONFLICT clauses that name a constraint as the conflict target. HSQLDB supports upsert conflict clauses keyed by columns, but it has no 'ON CONFLICT ON CONSTRAINT <name>' syntax, so when conflictClause.isDoUpdate() and getConstraintName() != null Hibernate throws IllegalQueryOperationException during query translation.","triggerScenarios":"Running an HQL insert with a constraint-named conflict target on HSQLLegacyDialect, e.g. 'insert into Person (id, name) select ... on conflict on constraint uk_person_name do update set name = excluded.name'. Only the DO UPDATE form with a constraint name is rejected; DO NOTHING and column-target forms pass through.","commonSituations":"Sharing one upsert @NamedQuery or criteria InsertSelectStatement across HSQLDB (tests) and PostgreSQL/SQLite (production), then the constraint-name form leaks into the HSQLDB test run; migrating upsert statements from a dialect that supports named constraints to HSQLDB in-memory tests.","solutions":["Change the conflict target to a column list: 'on conflict (name) do update set ...' — supported on HSQLDB","Drop the conflict clause on HSQLDB and handle the unique-violation (DataIntegrityViolationException/ConstraintViolationException) yourself with a follow-up UPDATE","Move the upsert to a native query for the HSQLDB profile"],"exampleFix":"// before (fails on HSQLDB)\ninsert into Person (id, name) values (:id, :name)\n  on conflict on constraint uk_person_name do update set name = excluded.name\n\n// after\ninsert into Person (id, name) values (:id, :name)\n  on conflict (name) do update set name = excluded.name","handlingStrategy":"validation","validationCode":"static boolean usesConstraintConflictTarget(String hql) {\n    return hql.toLowerCase().matches(\"(?s).*on\\\\s+conflict\\\\s+on\\\\s+constraint.*\");\n}\nif ( session.getJdbcServices().getDialect() instanceof HSQLLegacyDialect\n        && usesConstraintConflictTarget(hql) ) {\n    throw new IllegalArgumentException(\"HSQLDB supports only column-target conflict clauses\");\n}","typeGuard":"static boolean isLegacyHsqldb(Dialect d) { return d instanceof HSQLLegacyDialect; }","tryCatchPattern":"try {\n    session.createQuery(hql).executeUpdate();\n} catch (IllegalQueryOperationException e) {\n    if ( String.valueOf(e.getMessage()).contains(\"constraint name\") ) {\n        // rewrite conflict target to a column list and retry\n    }\n    throw e;\n}","preventionTips":["Prefer column-list conflict targets ('on conflict (col)') over constraint names - they are portable across Hibernate dialects","Keep upsert HQL in one place so dialect-specific variants are easy to maintain","Exercise upsert queries in the same database profile used in production, not only in the fastest test DB"],"tags":["hibernate","hsqldb","upsert","on-conflict","insert"],"backgroundTag":"insert-on-conflict-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}