Add formatting to first arguments in Query Conditions: PostgreSQL search case

There already was a complaint about PostgreSQL case-sensitive search. If you type “peter”, it won’t return Peter.

I found an ugly workaround which might help in most cases. For every field to check against the search, you should compare with the variable and with its capitalied version. So it will check against “peter” and “Peter”.

The con of this is if there’s a second word, as it is not getting capitalized.

The things would be much easier if flollowing this advice, we could use coding and formatting the left part as well:

WHERE LOWER(users.first_name ) = LOWER('%_GET.name')

Then we would compare strings not worring about the case.

As Patrick replied in my post that you have linked, they don’t plan to update the query builder anytime soon.
Changing the left column is not a good option as it will make this UI very complex.

The work-around we use is that we save all such “searchable” values in the DB in lowercase. And in such query conditions, we just use lower formatter.
On client side, we use the capitalize/upper formatter to show values correctly.

Its not very convenient, but much better and practical than having to write custom queries as Patrick had suggested.

1 Like

Thank you for the workaround. I think, it’s better then to create an extra “searchable” property using PostreSQL lower() function. And for output show the original column. Otherwise some exotic names would be shown incorrectly, like Charles de Batz de Castelmore d’Artagnan.

1 Like

Thats right. But this will be practical only until a certain point as well. Can't keep creating duplicate columns just because Wappler's builder UI can't write the query we want.
A better suggestion would be to just use ALL CAPS to display these values in UI.
And for some critical fields like name, you can have duplicate columns in DB.

1 Like