Thread: Flag for insentive use of Postgres?

Flag for insentive use of Postgres?

From
Andreas Schlegel
Date:
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



Re: Flag for insentive use of Postgres?

From
Stephane Bortzmeyer
Date:
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.


Re: Flag for insentive use of Postgres?

From
Adrian 'Dagurashibanipal' von Bidder
Date:
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

Re: Flag for insentive use of Postgres?

From
Andrew Sullivan
Date:
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


Re: Flag for insentive use of Postgres?

From
Stephane Bortzmeyer
Date:
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.

Re: Flag for insentive use of Postgres?

From
nconway@klamath.dyndns.org (Neil Conway)
Date:
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

Re: Flag for insentive use of Postgres?

From
Andreas Schlegel
Date:
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



Re: Flag for insentive use of Postgres?

From
Stephane Bortzmeyer
Date:
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.




Re: Flag for insentive use of Postgres?

From
Oleg Bartunov
Date:
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


Re: Flag for insentive use of Postgres?

From
Arjen van der Meijden
Date:
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


Re: Flag for insentive use of Postgres?

From
Stephane Bortzmeyer
Date:
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?