Thread: Flag for insentive use of Postgres?
Hi, is there a flag to set which determines if queries run successful with a case sensitive or case insensitive WHERE clause: For example: if I have a name1 colum with a value 'Test' I would like to search for 'test' and get also this row (select * from tTest where name1='test') Greetings, Andreas
On Wed, Jul 10, 2002 at 02:03:30PM +0200, Andreas Schlegel <schlegel@software.b.uunet.de> wrote a message of 18 lines which said: > is there a flag to set which determines if queries run successful with a > case sensitive or case insensitive WHERE clause: What if the encoding is Unicode, which has no case-folding rules? > For example: if I have a name1 colum with a value 'Test' I would like to > search for 'test' and get also this row > (select * from tTest where name1='test') And name1 = 'MASSE' when you requested name1 = 'Maße'? So, even with Latin-1, you have a problem.
On Wed, 2002-07-10 at 14:31, Stephane Bortzmeyer wrote: > On Wed, Jul 10, 2002 at 02:03:30PM +0200, > Andreas Schlegel <schlegel@software.b.uunet.de> wrote > a message of 18 lines which said: > > > is there a flag to set which determines if queries run successful with a > > case sensitive or case insensitive WHERE clause: > > What if the encoding is Unicode, which has no case-folding rules? Unicode *has* case-folding rules. But they are quite complex (and I'm not an expert - perhaps there's some problem with them?) http://www.unicode.org/unicode/reports/tr21/ cheers -- vbi -- secure email with gpg http://fortytwo.ch/gpg
Attachment
On Wed, Jul 10, 2002 at 02:03:30PM +0200, Andreas Schlegel wrote:
> Hi,
>
> is there a flag to set which determines if queries run successful with a
> case sensitive or case insensitive WHERE clause:
>
> For example: if I have a name1 colum with a value 'Test' I would like to
> search for 'test' and get also this row
> (select * from tTest where name1='test')
If all you want is the values to match without case sensitivity, use
ILIKE. (The rules are different in different locales, and I don't
even know how ILIKE works for non-C locales.)
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
On Wed, Jul 10, 2002 at 03:44:56PM +0200,
Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> wrote
a message of 39 lines which said:
> Unicode *has* case-folding rules. But they are quite complex (and I'm
> not an expert - perhaps there's some problem with them?)
Many. For instance, upper('é') -> É which is technically correct but
very few Frenchmen will accept that STEPHANE (without the É) is not
the uppercase of Stéphane.
If you want user-friendliness in a application, you cannot rely on
those rules.
On Wed, Jul 10, 2002 at 02:03:30PM +0200, Andreas Schlegel wrote: > For example: if I have a name1 colum with a value 'Test' I would like to > search for 'test' and get also this row > (select * from tTest where name1='test') http://www.ca.postgresql.org/docs/faq-english.html#4.12 Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Stephane Bortzmeyer wrote: > >>For example: if I have a name1 colum with a value 'Test' I would like to >>search for 'test' and get also this row >>(select * from tTest where name1='test') > > > And name1 = 'MASSE' when you requested name1 = 'Maße'? So, even with > Latin-1, you have a problem. > In the meantime I got a message with a quite good solution: ... WHERE lower(name1)='test' The mentioned problem with phrases like 'Maße' is in this application no problem: For every field value the programm creates some matchcodes which contain the original value (Maße) and values with replaced characters( ß -> ss, Ä -> Ae ...). So the user doesn't care if he is looking for 'Maße' or 'Masse'. Greetings, Andreas
On Wed, Jul 10, 2002 at 10:24:15AM -0400,
Andrew Sullivan <andrew@libertyrms.info> wrote
a message of 28 lines which said:
> ILIKE. (The rules are different in different locales, and I don't
> even know how ILIKE works for non-C locales.)
I've not been able to make it work. With all variables set to a French
locale (which works fine with all the programs), PostgreSQL, with
ILIKE or upper(), is still case-sensitive. (upper('stéphane') does not
match STEPHANE nor STÉPHANE.)
It does not bother me, since I'm convinced that the problem is far
too complicated to be solved by simple case-folding rules like those of
US-ASCII.
On Wed, 10 Jul 2002, Stephane Bortzmeyer wrote:
> On Wed, Jul 10, 2002 at 10:24:15AM -0400,
> Andrew Sullivan <andrew@libertyrms.info> wrote
> a message of 28 lines which said:
>
> > ILIKE. (The rules are different in different locales, and I don't
> > even know how ILIKE works for non-C locales.)
>
> I've not been able to make it work. With all variables set to a French
> locale (which works fine with all the programs), PostgreSQL, with
> ILIKE or upper(), is still case-sensitive. (upper('stИphane') does not
> match STEPHANE nor STиPHANE.)
it should works ! Check if you define locale environment *just before*
starting postmaster. I've been asked many times why locale doesnt' works
here in Russia and every time there were problem with locale or startup
scripts.
>
> It does not bother me, since I'm convinced that the problem is far
> too complicated to be solved by simple case-folding rules like those of
> US-ASCII.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
In the postgresql manual you find some stuff like ILIKE here: http://www.postgresql.org/idocs/index.php?functions-matching.html Andreas Schlegel wrote: > Hi, > > is there a flag to set which determines if queries run successful with a > case sensitive or case insensitive WHERE clause: > > For example: if I have a name1 colum with a value 'Test' I would like to > search for 'test' and get also this row > (select * from tTest where name1='test') > > Greetings, > Andreas > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Wed, Jul 10, 2002 at 07:13:40PM +0300, Oleg Bartunov <oleg@sai.msu.su> wrote a message of 38 lines which said: > it should works ! Check if you define locale environment *just before* > starting postmaster. You mean it cannot be done on a per-user basis? What if I have a Unicode database and users from all Europe, using different locales?