Home Postgres search using lower() index
Reply: 0

Postgres search using lower() index

user864
1#
user864 Published in April 26, 2018, 1:44 pm

I have a Postgres table containing column with stings in upper and lower case characters.

Table: company
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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302233 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO