Case insensitive array query Flask-SQLAlchemy

2
votes

I have a text array column in a Postgres table containing user's email and alt email addresses.

I'm using Flask and SQLAlchemy and I want to query the database for the row where the user's inputed email is contained in the array. At the moment I'm using .contains

Users.query.filter(Users.emails.contains({email})).first()

The issue I'm having is that some of the existing email addresses have a mixture of upper and lower case however I can't seem to find a method for making an Array lower or the query case insensitive.

I've looked at func.lower but this doesn't want to play nicely with Arrays, the other option is .ilike however I can't seem to get this to work and my table is approx 10,000 rows and .ilike I don't think will be the most efficient?

What is the best way to do this?

1
pythonpostgresqlflasksqlalchemyflask-sqlalchemy
A dirty solution would be to just cast the array to text, lower(), and check. Not very performance friendly, though. A cleaner approach is a bit more involved, but have a look at unnest() and array constructors. - Ilja Everilä
@IljaEverilä, thanks a lot for a pointer to unnest()! - egor83

1 Answers

-1
votes

I ended up just using raw SQL, which did the trick.

db.engine.execute("SELECT * FROM Users WHERE '%s' = ANY(lower(emails::text)::text[])" % email)