Thread: Disabling case sensitivity

Disabling case sensitivity

From
linux_211@hotmail.com (igor)
Date:
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

Re: Disabling case sensitivity

From
Stephane Bortzmeyer
Date:
On Tue, Jul 09, 2002 at 04:11:38PM -0700,
 igor <linux_211@hotmail.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'?


Re: Disabling case sensitivity

From
Oliver Elphick
Date:
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()).



Re: Disabling case sensitivity

From
Michael Meskes
Date:
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@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Re: Disabling case sensitivity

From
Stephane Bortzmeyer
Date:
On Wed, Jul 10, 2002 at 03:22:00PM +0200,
 Michael Meskes <meskes@postgresql.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();

Re: Disabling case sensitivity

From
linux_211@hotmail.com (igor)
Date:
> > 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?

Re: Disabling case sensitivity

From
ktt
Date:
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@hotmail.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

Re: Disabling case sensitivity

From
Andrew Sullivan
Date:
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@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110


Re: Disabling case sensitivity

From
Stephan Szabo
Date:
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).


Re: Disabling case sensitivity

From
"Arguile"
Date:
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) );



Re: Disabling case sensitivity

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

Attachment

Re: Disabling case sensitivity

From
linux_211@hotmail.com (igor)
Date:
>
> 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.

Re: Disabling case sensitivity

From
Stephan Szabo
Date:
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.


Re: Disabling case sensitivity

From
"Jan D'Hondt"
Date:
MS SQL server stores 'St�phane' as 'St�phane' which is exactly what people
would want. Your email server on the other hand...


Jan D'Hondt


Stephane Bortzmeyer <bortzmeyer@nic.fr> schreef in berichtnieuws
20020710125119.GA15584@nic.fr...
> On Tue, Jul 09, 2002 at 04:11:38PM -0700,
>  igor <linux_211@hotmail.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'?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org