CODEWITHSUNDEEP
sqlpostgresql
acm
acm

Reputation: 6637

Postgresql indexing lower-case(column) doesn't work as expected?

The following is an example of the data I can get when searching for "amadeus" substring:

db=# SELECT entidade FROM gma WHERE entidade ILIKE '%amadeus%';
            entidade
---------------------------------
 Hairdresser Amadeus
 Snack-Bar Amadeus
 Restaurant Amadeus
 Restaurant Amadeus
 Restaurant Amadeus
 Amadeus - Musical Instruments
(6 rows)

However I want to be able replace ILIKE by LIKE. So I tried to index entidade with only lower-case letters:

db=# CREATE INDEX idx_gma_entidade ON gma USING btree
db-#   ( lower(entidade) );
CREATE INDEX

By now I was expecting to access exactly the same data using LIKE:

db=# SELECT entidade FROM gma WHERE entidade LIKE '%amadeus%';
 entidade
----------
(0 rows)

But, as you can see, the result is not what I expected... Can somebody care to explain why? And, if possible, how can I achieve the expected behaviour?

Upvotes: 0

Views: 1384

Answers (4)

Aniket Singla
Aniket Singla

Reputation: 585

As everyone pointed out the query was not using your index. You can use explain analyse keywords in front of your query to find whether index was used or not.

Upvotes: 0

araqnid
araqnid

Reputation: 133422

Creating an index should never change the result of a query, only how it is implemented. You must specify ILIKE or a construct such as lower(column) LIKE '...' for a case-insensitive match against a text column. You can use the citext contrib module to create a citext type that will match case-insensitively by default, which seems to be what you want.

Upvotes: 1

user330315
user330315

Reputation:

You need to use the lower() function on the column when running your select:

SELECT entidade FROM gma WHERE lower(entidade) LIKE '%amadeus%';

But because you have a wildcard at the front, the query will never use an index anyway. So there is no point in creating one

Upvotes: 2

Frank Heikens
Frank Heikens

Reputation: 126991

You can't use btree indexes for this. Using a wildcard at the beginning of the search string makes an index useless.

Use full text search or take a look at wildspeed.

Upvotes: 2

Related Questions