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@sss.pgh.pa.us> wrote:
Robert DiFalco <robert.difalco@gmail.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.