Postgrest: Case insensitive queries are not supported.

[jacobreto]

I would like to use postgrest as an API front-end for a login database. However, I can't find any case insensitive equality operator to look up usernames in our database, e.g. we want to find "PlayerBob" even if a user types "playERBob", eq. SELECT * FROM users WHERE LOWER(username)='playerbob'.

We have an index on LOWER(username), so this is not bad (i.e. a serial query), but I can't find a way to generate and equal query through the postgrest API.

I couldn't find any signs that this is supported in neither the documentation or nor the source.

[steve-chavez]

You could do that with a view or with a computed column:

create function lower_username(users) returns text as $$ 
select lower($1.username) $$ language sql;

GET /users?lower_username=eq.playerbob

[jacobreto]

That's such an elegant solution, thank you so very much! Do you know the resulting query is indexed?

[begriffs]

There's also the "citext" extension that comes with postgres. It provides a perfect case insensitive type for this column. Works with indices and doesn't require a computed column.

For an example, see the email address section of my article:
https://begriffs.com/posts/2017-10-21-sql-domain-integrity.html#email-addresses

[jacobreto]

Perfect. Thank you very much for writing this!

[jacobreto]

Here is a small followup:

Both of the proposed solutions work. However, both of them have issues when used on a database with multiple schemas. We have a public, and then we have login, which is the db-schema that I have selected in the config file passed on to postgrest.

When I try to do a computed column, it works great if the function lies under the public schema, but not if it lies in the login schema, which is a bit odd since that is what I selected in the config file.

Here is what it looks like when lower_username lies in login:
$ curl -s "localhost:3000/users?lower_username=eq.testuser" {"hint":null,"details":null,"code":"42703","message":"column users.lower_username does not exist"}

If I try to do $ curl -s "localhost:3000/users?login.lower_username=eq.testuser, the entire users table is dumped!!

If we don't find another solution, we will let the lower_username function live in public since it's the best option we seem to have right now.

citext falls back to case-sensitive comparisons when out of scope. It works for me, but other developers in other applications found that it is a bit hit-and-miss.

I am _almost_ inclined to suggest that a cieq would be a nice addition to the flock.

[jacobreto]

Nevermind, fixed computed column issue by adding login to the user's search_path. Sorry, I'm too new at postgresql for this.

https://stackoverflow.com/questions/39261729/additional-module-citext-is-installed-but-type-citext-not-found

[ruslantalpa]

Check your postgrest version. The function in the login schema should work in the latest version (or at least it is like that in the master)

On 27 Feb 2018, at 15:49, jacobreto [e-mail] wrote:

Closed #1073.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub, or mute the thread.