[Solved]-Case-insensitive search on a postgres ArrayField with django

by

4👍

✅

icontains, iexact are only applied on string type:

my_array_field__contains=['H']

check if [‘H’] is included in my_array_field

But if you try the following, it will works:

my_array_field__0__icontains='H'

because it check if the first element contains H or h

👤Dhia

8👍

warning: this is a hack and led to a bug in my own app. Use at your own risk.

You can do a case-insensitive look-up using icontains, by omitting the square brackets you would use for contains:

queryset = my_model.objects.filter(field_name__icontains='my_substring')

This works because Postgres casts the ArrayField to text and then checks for ‘my_substring’ as a substring of the array’s text representation. You can see this by examining the resulting query:

print(queryset.query)

# raw SQL output:
'SELECT ... WHERE UPPER("my_model"."field_name"::text) LIKE UPPER(%my_substring%)

This worked for me in Postgres 10.1.

addendum/bug

This approach caused a bug for my project. Say you want to check if the array field contains 'my_substring':

field_name__icontains='my_substring'

This will retrieve 'my_substring', but it will also retrieve 'foo_my_substring'. This has to do with the string casting mentioned above. Use at your own risk.

👤Stevula

3👍

You can query case-insensive search for array like this:

select 'tartu' ILIKE ANY (array['Tallinn', 'Tartu','Narva']);

In Django:

MyModel.objects.extra(
    where=['%s ILIKE ANY (array_column_name)'],
    params=['tartu', ]
)
👤quick

Leave a comment