{"record":{"id":"d640fc2051d5bc00","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint-d640fc","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/HSQLSqlAstTranslator.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-core/src/main/java/org/hibernate/dialect/sql/ast/HSQLSqlAstTranslator.java#L75-L111","documentation":"Hibernate renders HQL/JPA INSERT ... ON CONFLICT (upsert) statements per dialect. HSQLDB has no native ON CONFLICT, so Hibernate emulates the upsert, and the emulation can only target conflict columns -- never a named constraint. When the SQM ConflictClause has both isDoUpdate() and a non-null constraintName (HQL 'on conflict on constraint <name> do update'), HSQLSqlAstTranslator.visitConflictClause rejects it with IllegalQueryOperationException while the SQL is being rendered, before anything reaches the database.","triggerScenarios":"Creating/executing HQL like 'insert into Person (id,name) values (:id,:n) on conflict on constraint uk_person_name do update set name = excluded.name' (or building the same via SqmInsertSelectStatement.getConflictClause().conflictOnConstraint(\"uk_...\")) while the SessionFactory runs on HSQLDialect. The throw happens at query translation time (createQuery/executeUpdate), not at DB execution time.","commonSituations":"Running PostgreSQL-targeted upsert HQL (PostgreSQL accepts 'on constraint <name>') against in-memory HSQLDB tests; sharing one test-suite/persistence XML across dialects; adopting the HQL 'on conflict' clause added in Hibernate 6.6+ and assuming the constraint-name form is portable.","solutions":["Replace the constraint-name conflict target with a column list: 'on conflict (id) do update set ...' -- the HSQLDB emulation supports column-based targets","For HSQLDB runs, drop the conflict clause entirely and catch the constraint violation (SQLIntegrityConstraintViolationException) on the plain insert","Guard the HQL by dialect: only use 'on conflict on constraint' on dialects whose translators render it (e.g. PostgreSQL family)","If the exact upsert semantics are required on HSQLDB, use a native MERGE INTO query"],"exampleFix":"// before\nem.createQuery(\"insert into Person p (p.id,p.name) values (:id,:n)\"\n    + \" on conflict on constraint uk_person_name do update set p.name = excluded.name\");\n\n// after (column-based conflict target works on HSQLDB)\nem.createQuery(\"insert into Person p (p.id,p.name) values (:id,:n)\"\n    + \" on conflict (id) do update set p.name = excluded.name\");","handlingStrategy":"validation","validationCode":"// Before building upsert HQL, check the dialect can target a named constraint\nstatic boolean supportsConstraintNameConflictTarget(Dialect dialect) {\n    // PostgreSQL-family translators render \"on conflict on constraint\";\n    // HSQLDB does not -- extend this allow-list as you add dialects\n    return dialect instanceof PostgreSQLDialect;\n}\n\nString hql = supportsConstraintNameConflictTarget(dialect)\n    ? \"... on conflict on constraint uk_x do update ...\"\n    : \"... on conflict (id) do update ...\";","typeGuard":null,"tryCatchPattern":"try {\n    em.createQuery(hql).executeUpdate();\n} catch (IllegalQueryOperationException e) {\n    // translation-time rejection: rewrite the conflict clause with a column target and retry\n    log.warn(\"Constraint-name conflict target unsupported on {}\", dialect.getClass().getSimpleName());\n    throw new UnsupportedOperationException(\"Rewrite upsert with column conflict target\", e);\n}","preventionTips":["Standardize on column-list conflict targets ('on conflict (cols)') in shared HQL -- they work on every dialect","Keep dialect-specific HQL in per-dialect fragments instead of one universal string","Run the upsert test suite against every dialect in the build matrix (HSQLDB included) before merging"],"tags":["hibernate","hsqldb","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"}