Re: Locale/Indexing

On Thu, Nov 10, 2005 at 12:56:26PM -0500, Tamas Hegedus wrote:
> Hi,
>
> I was using locale="C" (ANSI_SQL) in the last years as ONLY this case
> indexes were used in queries containing """... LIKE 'someString%' """.
>
> I would like to access my databases through JDBC (I am new to it; I am
> not a programmer). For JDBC it is highly recommended not to use ANSI_SQL
> encoding. Note: I would like to use non-ANSI_SQL encoding. I would like
> to avoid taking care of non-ansi-sql charachters...

This question seems to come up a lot these days. If you want to use
LIKE 'abc%' type queries with indexes you need to declare your index
like this:

CREATE INDEX myindex ON mytable(myfield text_pattern_ops);

This changes the ordering so LIKE queries work, doesn't matter what
locale.

> => Does this mean that through JDBC I can not use ANSI_SQL implying I
> can not use indexes in 'LIKE' queries?

Declare your idexes appropriatly and you can use LIKE as much as you
want.

> => Which encoding is the best/most common to use?

UTF-8 is the most flexible all round...

> PS: How to setup to receive emails from the list not in a digest form? I
> could not figure out.

Login into your mailman config and change it:
http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org?user=&passw=&func=show

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.