CODEWITHSUNDEEP
sqlpostgresqlindexingpostgresql-9.3postgresql-9.4
Jimski
Jimski

Reputation: 944

Postgres search using lower() index

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.

Upvotes: 2

Views: 2900

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

The expression:

where lower(company.department) = lower($1)),

does not defeat the purpose of having a lower-case index. It is exactly the reason why you want to have one. What is important for using the index is the expression lower(company.department), because this is the expression used to build the index.

Upvotes: 2

klin
klin

Reputation: 121794

I don't want to use: ...where lower(company.department) = lower($1)), because it defeats the purpose of having a lowercase index.

On the contrary, the index will only be used if you use exactly the same expression in the query as in the index. See Indexes on Expressions in the documentation.

Upvotes: 4

Related Questions