Re: Disabling case sensitivity

Hi

I would like to know if there is some way how to disable case
sensitivity in PostgreSQL server. With some varaiable or some setting
in configuration file or how?
So after this would be the same 'Good' and 'GOOD' or 'GoOd' in some
SELECT from table where username='Good'. I wanna get the same row
with:
SELECT from table where username='GoOd';
Exactly like in MS SQL server.

Thanks a lot for any help

On Tue, Jul 09, 2002 at 04:11:38PM -0700,
igor <linux_211(at)hotmail(dot)com> wrote
a message of 12 lines which said:

> I would like to know if there is some way how to disable case
> sensitivity in PostgreSQL server.

Always remember that case-INsensitivity is properly defined only for
US-ASCII. Many PostgreSQL users store data in other scripts like
Latin-1.

> Exactly like in MS SQL server.

What does MS SQL server does with Unicode? Does it map 'Stéphane' to
'STEPHANE'?

On Wed, 2002-07-10 at 00:11, igor wrote:

> I would like to know if there is some way how to disable case
> sensitivity in PostgreSQL server. With some varaiable or some setting
> in configuration file or how?
> So after this would be the same 'Good' and 'GOOD' or 'GoOd' in some
> SELECT from table where username='Good'. I wanna get the same row
> with:
> SELECT from table where username='GoOd';
> Exactly like in MS SQL server.

You can use ILIKE, or search for a match to lower('GoOd') (or upper()).

On Tue, Jul 09, 2002 at 04:11:38PM -0700, igor wrote:
> I would like to know if there is some way how to disable case
> sensitivity in PostgreSQL server. With some varaiable or some setting
> in configuration file or how?
> So after this would be the same 'Good' and 'GOOD' or 'GoOd' in some
> SELECT from table where username='Good'. I wanna get the same row
> with:
> SELECT from table where username='GoOd';

Can't you use something like

SELECT from table where tolower(username)='good';

?

> Exactly like in MS SQL server.

You mean MS SQL is not case sensitive for data? But it is for attribute
names. Not exactly what I call a logical setup. :-)

Michael
--
Michael Meskes
Michael(at)Fam-Meskes(dot)De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

On Wed, Jul 10, 2002 at 03:22:00PM +0200,
Michael Meskes <meskes(at)postgresql(dot)org> wrote
a message of 31 lines which said:

> Can't you use something like
>
> SELECT from table where tolower(username)='good';

If you want sometimes case-sensitive comparison and sometimes not, it
is a good solution (the PostgreSQL extension ILIKE is another). But if
you want to "disable case-sensitivity", you risk that some
applications forget the call to tolower(). Therefore, I prefer to
create a trigger which will force the field to lowercase before
INSERTing it. (The problem of my solution is that it is no longer
case-preserving.)

CREATE FUNCTION force_lower_case() RETURNS OPAQUE
AS 'BEGIN
NEW.name = lower(NEW.name);
RETURN NEW;
END;'
LANGUAGE PLPGSQL;

-- Domain names are only in US-ASCII (so no locale problems) and are
-- case-insensitive. If you want to record the original case, add a
-- new field.
CREATE TRIGGER force_lower_case
BEFORE INSERT ON Domains
FOR EACH ROW
EXECUTE PROCEDURE force_lower_case();

> > Can't you use something like
> >
> > SELECT from table where tolower(username)='good';
>
> If you want sometimes case-sensitive comparison and sometimes not, it
> is a good solution (the PostgreSQL extension ILIKE is another). But if
> you want to "disable case-sensitivity", you risk that some
> applications forget the call to tolower(). Therefore, I prefer to
> create a trigger which will force the field to lowercase before
> INSERTing it. (The problem of my solution is that it is no longer
> case-preserving.)
>
> CREATE FUNCTION force_lower_case() RETURNS OPAQUE
> AS 'BEGIN
> NEW.name = lower(NEW.name);
> RETURN NEW;
> END;'
> LANGUAGE PLPGSQL;
>
> -- Domain names are only in US-ASCII (so no locale problems) and are
> -- case-insensitive. If you want to record the original case, add a
> -- new field.
> CREATE TRIGGER force_lower_case
> BEFORE INSERT ON Domains
> FOR EACH ROW
> EXECUTE PROCEDURE force_lower_case();
>
This is not gonna to work for me. I can't to use ILIKE or something
like that because the program is already written ,and I can't change
the code. There must to be some way how to completely disable all
case-sensitivity from the server without change the code, no?

That's a problem, because I building
a UNICODE text database and planning case insensitive
search.
Shoul all case insensitive search be
delivered to PHP or other scripting language?

ktt

--- igor <linux_211(at)hotmail(dot)com> wrote:
> > > Can't you use something like
> > >
> > > SELECT from table where
> tolower(username)='good';
> >
> > If you want sometimes case-sensitive comparison
> and sometimes not, it
> > is a good solution (the PostgreSQL extension ILIKE
> is another). But if
> > you want to "disable case-sensitivity", you risk
> that some
> > applications forget the call to tolower().
> Therefore, I prefer to
> > create a trigger which will force the field to
> lowercase before
> > INSERTing it. (The problem of my solution is that
> it is no longer
> > case-preserving.)
> >
> > CREATE FUNCTION force_lower_case() RETURNS OPAQUE
> > AS 'BEGIN
> > NEW.name = lower(NEW.name);
> > RETURN NEW;
> > END;'
> > LANGUAGE PLPGSQL;
> >
> > -- Domain names are only in US-ASCII (so no locale
> problems) and are
> > -- case-insensitive. If you want to record the
> original case, add a
> > -- new field.
> > CREATE TRIGGER force_lower_case
> > BEFORE INSERT ON Domains
> > FOR EACH ROW
> > EXECUTE PROCEDURE force_lower_case();
> >
> This is not gonna to work for me. I can't to use
> ILIKE or something
> like that because the program is already written
> ,and I can't change
> the code. There must to be some way how to
> completely disable all
> case-sensitivity from the server without change the
> code, no?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

On Wed, Jul 10, 2002 at 12:36:20PM -0700, igor wrote:

> the code. There must to be some way how to completely disable all
> case-sensitivity from the server without change the code, no?

I guess you could edit the sources, but I frankly cannot understand
the point of the request. If some system has decided that
'BoBsYouRUNcLE'='bobsyouruncle', then as far as I'm concerned, that
system is badly broken. They're not the same string. I should be
very annoyed not to be able to tell the difference.

Being able to ignore the difference strictly in terms of case
(appropriately defined by locale) is what things like ILIKE are for.
But "=" means "the same", not "sort of the same".

If your application can't be fixed, and relies entirely on some
(non-)feature of some other system, well, then, you have to use that
other system. Them's the breaks.

A

--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110

On Thu, 11 Jul 2002, ktt wrote:

>
> That's a problem, because I building
> a UNICODE text database and planning case insensitive
> search.

You can do case insensitive searches as long as you're
willing to use something other than var=literal (such
as tolower(var)=lowerliteral or tolower(var)=tolower(literal)
or var ILIKE literal).

Stephan Szabo wrote:
>
> On Thu, 11 Jul 2002, ktt wrote:
>
> >
> > That's a problem, because I building
> > a UNICODE text database and planning case insensitive
> > search.
>
> You can do case insensitive searches as long as you're
> willing to use something other than var=literal (such
> as tolower(var)=lowerliteral or tolower(var)=tolower(literal)
> or var ILIKE literal).
>

If your general search is case insensitive, remeber you can use functional
indices to improve performance.

CREATE INDEX foo ON bar( upper(qux) );

On Thu, 2002-07-11 at 18:22, Stephan Szabo wrote:
> On Thu, 11 Jul 2002, ktt wrote:
>
> >
> > That's a problem, because I building
> > a UNICODE text database and planning case insensitive
> > search.
>
> You can do case insensitive searches as long as you're
> willing to use something other than var=literal (such
> as tolower(var)=lowerliteral or tolower(var)=tolower(literal)
> or var ILIKE literal).

As was pointed out this will not work in the general case for non-ascii.
I think it is necessary to code an explicit case insensitive and locale
aware string compare function. (the libc strcoll function seems to do
exactly that, except that it seems to be case sensitive, whereas
strncasecomp does not respect the locale, while it knows about charsets
and case).

cheers
-- vbi

--
secure email with gpg http://fortytwo.ch/gpg

>
> I guess you could edit the sources, but I frankly cannot understand
> the point of the request. If some system has decided that
> 'BoBsYouRUNcLE'='bobsyouruncle', then as far as I'm concerned, that
> system is badly broken. They're not the same string. I should be
> very annoyed not to be able to tell the difference.
>
The system(code) was originally written for MS SQL server ,what I am
doing now is only migration MS SQL -> PostgreSQL server. There is a
possibility in MS SQL server to choose between case-sensitive or
case-insensitive seraches. By default it was case-insensitive ,so code
was written for case-insensitive searches. I think it is not a very
big deal,when you need to choose for example November ,or november
,that is what I want for example.(November=november)

> Being able to ignore the difference strictly in terms of case
> (appropriately defined by locale) is what things like ILIKE are for.
> But "=" means "the same", not "sort of the same".
>
In case-insensitive world yes.

> If your application can't be fixed, and relies entirely on some
> (non-)feature of some other system, well, then, you have to use that
> other system. Them's the breaks.
>

Or wait until (if so) there will be possibility in POstgreSQL server
to make smething like case-insensitive searches.

On 15 Jul 2002, igor wrote:

> > Being able to ignore the difference strictly in terms of case
> > (appropriately defined by locale) is what things like ILIKE are for.
> > But "=" means "the same", not "sort of the same".
> >
> In case-insensitive world yes.
>
>
> > If your application can't be fixed, and relies entirely on some
> > (non-)feature of some other system, well, then, you have to use that
> > other system. Them's the breaks.
> >
>
> Or wait until (if so) there will be possibility in POstgreSQL server
> to make smething like case-insensitive searches.

If you have a locale where those characters are considered the same,
presumably you'd get case-insensitive searches if the database was made in
that locale. Barring that, you have source, you could go in and muck with
the appropriate functions.

MS SQL server stores 'Stphane' as 'Stphane' which is exactly what people
would want. Your email server on the other hand...

Jan D'Hondt

Stephane Bortzmeyer <bortzmeyer(at)nic(dot)fr> schreef in berichtnieuws
20020710125119(dot)GA15584(at)nic(dot)fr(dot)(dot)(dot)
> On Tue, Jul 09, 2002 at 04:11:38PM -0700,
> igor <linux_211(at)hotmail(dot)com> wrote
> a message of 12 lines which said:
>
> > I would like to know if there is some way how to disable case
> > sensitivity in PostgreSQL server.
>
> Always remember that case-INsensitivity is properly defined only for
> US-ASCII. Many PostgreSQL users store data in other scripts like
> Latin-1.
>
> > Exactly like in MS SQL server.
>
> What does MS SQL server does with Unicode? Does it map 'Stphane' to
> 'STEPHANE'?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org