hibernate/hibernate-orm · error · IllegalArgumentException

PostgreSQL and CockroachDB only support the case insensitive

Error message

PostgreSQL and CockroachDB only support the case insensitive flag 'i' as literal.

What it means

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.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/RegexpLikeOperatorFunction.java:44

	}

	@Override
	public void render(
			SqlAppender sqlAppender,
			List<? extends SqlAstNode> arguments,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> walker) {
		final boolean caseSensitive;
		if ( arguments.size() > 2 ) {
			if ( !(arguments.get( 2 ) instanceof Literal literal)
				|| !(literal.getLiteralValue() instanceof String flags)
				|| !flags.equals( "i" ) ) {
				if ( supportsStandard ) {
					super.render( sqlAppender, arguments, returnType, walker );
					return;
				}
				else {
					throw new IllegalArgumentException(
							"PostgreSQL and CockroachDB only support the case insensitive flag 'i' as literal." );
				}
			}
			caseSensitive = false;
		}
		else {
			caseSensitive = true;
		}

		sqlAppender.appendSql( '(' );
		arguments.get( 0 ).accept( walker );
		sqlAppender.appendSql( caseSensitive ? "~" : "~*" );
		arguments.get( 1 ).accept( walker );
		sqlAppender.appendSql( ')' );
	}

	@Override
	public boolean isPredicate() {

View on GitHub (pinned to fad1729dce)

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

Example fix

// before
where regexp_like(e.name, :pattern, 'im')

// after
where regexp_like(e.name, '(?im)' || :pattern)   -- only 'i' is accepted as a flag literal

Defensive patterns

Strategy: validation

Validate before calling

// PostgreSQL/CockroachDB: only the literal 'i' is a valid regexp_like flag
static String sanitizePgFlags(String flags) {
    if (flags == null || flags.isEmpty()) return null;
    if (!"i".equals(flags)) {
        throw new IllegalArgumentException("Only flag 'i' is supported, got: " + flags);
    }
    return flags;
}

Try / catch

try {
    return em.createQuery(hql).getResultList();
} catch (IllegalArgumentException e) {
    if (e.getMessage() != null && e.getMessage().contains("case insensitive flag")) {
        return em.createQuery(foldFlagsIntoPattern(hql)).getResultList(); // '(?i)' prefix
    }
    throw e;
}

Prevention

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

When it happens

Trigger: HQL: regexp_like(col, 'foo.*', 'g') or regexp_like(col, 'pat', :flags) on the PostgreSQL or CockroachDB dialects.

Common situations: Porting Oracle-style regexp_like flags to PostgreSQL via HQL; flag strings built at runtime; code shared between Oracle (rich flags) and PostgreSQL backends.

Related errors

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/29cfdcba93f7cf00. Report an issue: GitHub.