I have a Postgres table containing column with stings in upper and lower case characters.
id| name | department
1 | LM Corp | Repair
2 | BMG Inc | Maintenance
3 | DFR LLC | shipping
Using pgAdmin I created an index on the column as follows:
CREATE INDEX companydepartment_index
ON public.company (lower(department) ASC NULLS LAST);
When I do a query where input string is lowercase 'repair' then I don't get a match.
select company.id from company where company.department = lower($1));
Why isn't Postgres using the lowercase index to match the lowercase string?
I don't want to use: ...where lower(company.department) = lower($1)), because it defeats the purpose of having a lowercase index.