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?

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

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.

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.

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.