Flag for insentive use of Postgres?

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.

In the postgresql manual you find some stuff like ILIKE here:
http://www.postgresql.org/idocs/index.php?functions-matching.html

Andreas Schlegel wrote:

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

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

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?