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


icontains, iexact are only applied on string type:


check if [‘H’] is included in my_array_field

But if you try the following, it will works:


because it check if the first element contains H or h



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:


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

This worked for me in Postgres 10.1.


This approach caused a bug for my project. Say you want to check if the array field contains '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.


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

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

In Django:

    where=['%s ILIKE ANY (array_column_name)'],
    params=['tartu', ]

Leave a comment