PSQL Case insensitive search using Exposed

Question

How can we do case insensitive search using Exposed(Kotlin) on a postgres sql database?

SELECT users.id, users.name, users.created_at, users.updated_at FROM users 
WHERE users.name iLIKE '%aaa%'

There is like operator. I don't see ilike operator. Should I be using lowercase on the query field?

Answer 1

ILIKE is PostgreSQL specific function and doesn't support in Exposed at the moment, but you can define it by your own :

class ILikeOp(expr1: Expression<*>, expr2: Expression<*>) : ComparisonOp(expr1, expr2, "ILIKE")

infix fun<T:String?> ExpressionWithColumnType<T>.ilike(pattern: String): Op<Boolean> = ILikeOp(this, QueryParameter(pattern, columnType))