Content-Type: text/plain; charset=windows-1252
Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\))
Subject: Re: Does index based on text with 'en_US.utf8' collation support like
	query?
From: Alexey Klyukin <alexk@commandprompt.com>
In-Reply-To: <BLU0-SMTP2953C508D5F6127617CC45AB93A0@phx.gbl>
Date: Tue, 25 Dec 2012 14:44:58 +0200
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <F86B9A85-59E6-4577-856A-E6EE10B0D352@commandprompt.com>
References: <BLU0-SMTP2953C508D5F6127617CC45AB93A0@phx.gbl>
To: Haifeng Liu <liuhaifeng@live.com>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


Hello,

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

>=20
> Hi,
>=20
> 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 'd=
efault' which is 'en_US.utf8' in my database, the equation condition will u=
se the index but like query will not.

This is a documented behavior, LIKE with a default operator class only work=
s with C locale.

>=20
> I also tried to specify the collation explicitly: like ('abc%' collate 'e=
n_US.utf8'), not work too.
>=20
> 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 ot=
her collation?

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

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

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


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