pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Does index based on text with 'en_US.utf8' collation support like query?
2+ messages / 2 participants
[nested] [flat]

* Does index based on text with 'en_US.utf8' collation support like query?
@ 2012-12-25 11:37  Haifeng Liu <liuhaifeng@live.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Haifeng Liu @ 2012-12-25 11:37 UTC (permalink / raw)
  To: pgsql-admin

Hi,

I found that queries like "select * from tbl where col1 like 'abc%'" will use index only when the col1(and the index) use 'C' collation. If I use 'default' which is 'en_US.utf8' in my database, the equation condition will use the index but like query will not.

I also tried to specify the collation explicitly: like ('abc%' collate 'en_US.utf8'), not work too.

Is that only 'C' collation support 'like' query or I missed something in my sql? Or do I need to install something additional to support index on other collation?

Thanks in advance.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Does index based on text with 'en_US.utf8' collation support like query?
@ 2012-12-25 12:44  Alexey Klyukin <alexk@commandprompt.com>
  parent: Haifeng Liu <liuhaifeng@live.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Alexey Klyukin @ 2012-12-25 12:44 UTC (permalink / raw)
  To: Haifeng Liu <liuhaifeng@live.com>; +Cc: pgsql-admin


Hello,

On Dec 25, 2012, at 1:37 PM, Haifeng Liu <liuhaifeng@live.com> wrote:

> 
> Hi,
> 
> I found that queries like "select * from tbl where col1 like 'abc%'" will use index only when the col1(and the index) use 'C' collation. If I use 'default' which is 'en_US.utf8' in my database, the equation condition will use the index but like query will not.

This is a documented behavior, LIKE with a default operator class only works with C locale.

> 
> I also tried to specify the collation explicitly: like ('abc%' collate 'en_US.utf8'), not work too.
> 
> Is that only 'C' collation support 'like' query or I missed something in my sql? Or do I need to install something additional to support index on other collation?

You need to add a new index with a special operator class called 'text_pattern_ops' (or 'varchar_pattern_ops), i.e:
CREATE INDEX col1_pattern_idx ON tbl(col1 text_pattern_ops);
 
This index will be used in LIKE queries under non-C locale. You also need to preserve your existing default (aka text_ops) indexes if you want comparison or inequality operations on the col1 column to be indexed. 

Refer to http://www.postgresql.org/docs/current/static/indexes-opclass.html for more details.

--
Alexey Klyukin        http://www.commandprompt.com
The PostgreSQL Company – Command Prompt, Inc.






-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2012-12-25 12:44 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-12-25 11:37 Does index based on text with 'en_US.utf8' collation support like query? Haifeng Liu <liuhaifeng@live.com>
2012-12-25 12:44 ` Alexey Klyukin <alexk@commandprompt.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox