DB encoding, locale and indexes

From: Sterfield <sterfield(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: DB encoding, locale and indexes
Date: 2015-02-05 08:54:36
Message-ID: CAPf6=kcFWZLQawnyJCdw-QoZpWa7kkSm3SRKbbBsccUcgvS4AQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi everyone,

I'm a sysadmin working for an application that stores all its data in a PG
database.
Currently, the cluster has its encoding set to UTF-8, and the locale (both
LC_COLLATE and LC_CTYPE) is set to 'en_US.UTF-8'.

I discovered recently that the indexes created on varchar fields are not
working for LIKE operator, as they are created without the correct class
operator (as stated in
http://www.postgresql.org/docs/9.2/static/indexes-types.html)

The most straight-forward solution would be to create a second index on the
same field but with the class operator, in order to have indexes both for
=, >, < operators AND LIKE / regexp operators. Few additional indexes, some
diskspace eaten, problem solved.

However, some people are saying that nothing has to change on the index,
and that the only thing we have to do is to change the LC_COLLATE of each
databases to 'C', in order for the indexes to work without the class
operator.

So, we may have a database, with UTF-8 encoding, LC_TYPE to 'en_US.UTF-8'
and LC_COLLATE to 'C'.

This configuration seems to be really weird to me, that's why I'm asking
for your help here.

Few questions :

- Is it even possible ? (documentation seems to answer 'yes' to this
question, according to
http://www.postgresql.org/docs/current/static/multibyte.html)
- If I have unicode character stored in my database (for example 'é'),
and the LC_COLLATE set to 'C', how the index will behave if I do a query
with LIKE '<something>é%' ?

Many thanks for your help.
Guillaume.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guillaume Drolet 2015-02-05 13:28:59 Error: could not read symbolic link "pg_tblspc/940585". No such file or directory
Previous Message Matthew Kelly 2015-02-05 06:53:11 Re: [GENERAL] 4B row limit for CLOB tables