Thread: Locale/Indexing

Locale/Indexing

From
Tamas Hegedus
Date:
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...

=> Does this mean that through JDBC I can not use ANSI_SQL implying I
can not use indexes in 'LIKE' queries?
=> Which encoding is the best/most common to use?

Thanks,
Tamas

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

--
Tamas Hegedus, PhD          | phone: (1) 919-966 0329
UNC - Biochem & Biophys     | fax:   (1) 919-966 5178
5007A Thurston-Bowles Bldg  | mailto:hegedus@med.unc.edu
Chapel Hill, NC, 27599-7248 | http://biohegedus.org

Re: Locale/Indexing

From
Martijn van Oosterhout
Date:
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@svana.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.

Attachment