Re: Question about trigram GIST index

Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> writes:
> So, for my use case I simply need to search for a case insensitive
> substring. It need not be super exact. It seems like there are two ways I
> can do this:

> CREATE INDEX idx_users_name ON users USING GIST(lower(name) gist_trgm_ops);
> SELECT * FROM users WHERE lower(name) LIKE '%john%';

> Or I can do it like this:

> CREATE INDEX idx_users_name ON users USING GIST(name gist_trgm_ops);
> SELECT * FROM users WHERE name % 'john';

Hm, I don't see anything in the pg_trgm docs suggesting that % is
case-insensitive. But in any case, I'd go with the former as being
more understandable to someone who knows standard SQL.

regards, tom lane