I know! I was surprised that % 'John' or % 'JOHN' or even % 'jOhn' all returned the same result.
Besides readability would there be any technical differences between a GIST index that is lower or not and using LIKE vs. %?
Thanks!
On Thu, Dec 18, 2014 at 9:18 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Robert DiFalco <robert.difalco@xxxxxxxxx> 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
