Thread: Locale/Indexing
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
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.