Case-Insensitive Search In Postgres

Wappler General How To
#1

Hi.
I have setup a multi-query using complex conditions. One of those is a search filter, which uses “contains” operator.
But, the search executed on the DB is case sensitive. I searched for it and found that MySQL default to case insensitive search, but Postgres is csae sensitive.
To run case insensitive search, the query should look something like

SELECT subject FROM topics WHERE subject ~* 'eclipse';

Is there a way in Wappler to do this? The column part in conditions are dropdowns, so I can add a lowercase formatter there. Adding lowercase formatter just on the value part will not work either.
Please help.

Add formatting to first arguments in Query Conditions: PostgreSQL search case
Postgres need ILIKE
#2

Bump. @patrick

#3

The query builder only supports generic queries, syntax that is supported by SQL standard and not custom database specific features. If you want to use database specific features like the one above then you should use a custom query.

#4

Thanks for the reply.
Is there any other way to do a case-insensitive search using query builder?
I am not very familiar with PostgreSQL.

#5

Please check this workaround

#6

@patrick is there any new feature in Wappler regarding case-insentitive search in PosgreSQL or writing a custom query is the only option for now?

Postgres need ILIKE
#7

If you’re using PG, I would highly recommend utilising the native trigram indexing and query capability. It’s super performant and powerful. We search against 100M+ search terms at scale in avg. sub 100ms.

We dropped Elastic as our search engine in favour of it.

4 Likes