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 <t...@sss.pgh.pa.us> wrote: > > Robert DiFalco <robert.difa...@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. > > regards, tom lane >
