Re: index and ilke question

SHOW ALL list all show the value of a run-time parameter.

LC_COLLATE String sort order
LC_CTYPE Character classification (What is a letter? Its upper-case
equivalent?)
LC_MESSAGES Language of messages
LC_MONETARY Formatting of currency amounts
LC_NUMERIC Formatting of numbers
LC_TIME Formatting of dates and times

LC_COLLATE and LC_CTYPE is set on C, you just need index on the text field.

If not C then you can create an index with a special operator class (s.
documation 11.6).

----- Original Message -----
From: "Joost Kraaijeveld" <J(dot)Kraaijeveld(at)Askesis(dot)nl>
To: "Pgsql-General" <pgsql-general(at)postgresql(dot)org>
Sent: Sunday, September 11, 2005 10:49 AM
Subject: [GENERAL] index and ilke question

> Hi,
>
> I want to use the following query:
>
> select * from customers where lastname ilike 'jansen%'
>
> Explain says it uses a sequential scan on customers while there is an
> index on lastname (and 'jansen%' contains 1800 entries in a table of
> 370.000 customers so a index scan should be more logical?).
>
> The docs say "However, if your server does not use the C locale you will
> need to create the index with a special operator class to support
> indexing of pattern-matching queries."
>
> This seems to be the case as it does not use the index.
>
> Two questions:
>
> 1. How can I check if my (PostgreSQL or Linux?) server uses the C
> locale ?
>
> 2. And if it does not the (correct?) C locale is the syntax for a
> correct index the following, assuming that lastname is of type "text":
>
> CREATE INDEX test_index ON prototype.customers (lastname
> text_pattern_ops);
>
> (I tried this, but it did not change anything so I assume that either my
> assumptions about when to use an index as described above or my syntax
> are wrong)
>
> TIA
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J(dot)Kraaijeveld(at)Askesis(dot)nl
> web: www.askesis.nl
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>