{"record":{"id":"29cfdcba93f7cf00","repo":"hibernate/hibernate-orm","slug":"postgresql-and-cockroachdb-only-support-the-case-i","errorCode":null,"errorMessage":"PostgreSQL and CockroachDB only support the case insensitive flag 'i' as literal.","messagePattern":"PostgreSQL and CockroachDB only support the case insensitive flag 'i' as literal\\.","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/RegexpLikeOperatorFunction.java","lineNumber":44,"sourceCode":"\t}\n\n\t@Override\n\tpublic void render(\n\t\t\tSqlAppender sqlAppender,\n\t\t\tList<? extends SqlAstNode> arguments,\n\t\t\tReturnableType<?> returnType,\n\t\t\tSqlAstTranslator<?> walker) {\n\t\tfinal boolean caseSensitive;\n\t\tif ( arguments.size() > 2 ) {\n\t\t\tif ( !(arguments.get( 2 ) instanceof Literal literal)\n\t\t\t\t|| !(literal.getLiteralValue() instanceof String flags)\n\t\t\t\t|| !flags.equals( \"i\" ) ) {\n\t\t\t\tif ( supportsStandard ) {\n\t\t\t\t\tsuper.render( sqlAppender, arguments, returnType, walker );\n\t\t\t\t\treturn;\n\t\t\t\t}\n\t\t\t\telse {\n\t\t\t\t\tthrow new IllegalArgumentException(\n\t\t\t\t\t\t\t\"PostgreSQL and CockroachDB only support the case insensitive flag 'i' as literal.\" );\n\t\t\t\t}\n\t\t\t}\n\t\t\tcaseSensitive = false;\n\t\t}\n\t\telse {\n\t\t\tcaseSensitive = true;\n\t\t}\n\n\t\tsqlAppender.appendSql( '(' );\n\t\targuments.get( 0 ).accept( walker );\n\t\tsqlAppender.appendSql( caseSensitive ? \"~\" : \"~*\" );\n\t\targuments.get( 1 ).accept( walker );\n\t\tsqlAppender.appendSql( ')' );\n\t}\n\n\t@Override\n\tpublic boolean isPredicate() {","sourceCodeStart":26,"sourceCodeEnd":62,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/RegexpLikeOperatorFunction.java#L26-L62","documentation":"On PostgreSQL and CockroachDB Hibernate implements regexp_like via the ~ / ~* operators, which encode nothing but case sensitivity. The third (flags) argument must therefore be the literal string 'i' or absent; any other flag value or a non-literal flags expression throws IllegalArgumentException — unless the dialect declares standard regexp_like support (supportsStandard), in which case rendering is delegated to the standard implementation.","triggerScenarios":"HQL: regexp_like(col, 'foo.*', 'g') or regexp_like(col, 'pat', :flags) on the PostgreSQL or CockroachDB dialects.","commonSituations":"Porting Oracle-style regexp_like flags to PostgreSQL via HQL; flag strings built at runtime; code shared between Oracle (rich flags) and PostgreSQL backends.","solutions":["Drop the third argument for plain case-sensitive matching","Pass exactly the literal 'i' for case-insensitive matching","Fold other flag semantics into the pattern with inline modifiers, e.g. '(?i)foo'","Use native ~ / ~* / regexp_matches for advanced flag combinations"],"exampleFix":"// before\nwhere regexp_like(e.name, :pattern, 'im')\n\n// after\nwhere regexp_like(e.name, '(?im)' || :pattern)   -- only 'i' is accepted as a flag literal","handlingStrategy":"validation","validationCode":"// PostgreSQL/CockroachDB: only the literal 'i' is a valid regexp_like flag\nstatic String sanitizePgFlags(String flags) {\n    if (flags == null || flags.isEmpty()) return null;\n    if (!\"i\".equals(flags)) {\n        throw new IllegalArgumentException(\"Only flag 'i' is supported, got: \" + flags);\n    }\n    return flags;\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql).getResultList();\n} catch (IllegalArgumentException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"case insensitive flag\")) {\n        return em.createQuery(foldFlagsIntoPattern(hql)).getResultList(); // '(?i)' prefix\n    }\n    throw e;\n}","preventionTips":["Restrict regexp_like flags in HQL to null or the literal 'i' on PostgreSQL/CockroachDB","Fold multi-flag semantics into the pattern with inline modifiers","Use native ~ operators for flag-heavy regex work"],"tags":["hibernate","postgresql","cockroachdb","regexp-like","sql-flags"],"backgroundTag":"regex-flags-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}