Re: Question about trigram GIST index

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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
>