Thread: Re: case-insensitive database

No, I mean the data.

select * from stocks where symbol = 'AADBX'
and
select * from stocks where symbol = 'aadbx'

would bring up the same result set.


"Christopher Browne" <cbbrowne@acm.org> wrote in message
news:m3u17gsxhe.fsf@chvatal.cbbrowne.com...
> Quoth "Relaxin" <noname@spam.com>:
> > Is there a way to make Postgresql case-INSENSITIVE?
>
> It already is.
>
> portfolio=# select * from stocks limit 1;
>  symbol | description | exchange
> --------+-------------+----------
>  AADBX  | AADBX       | NYSE
> (1 row)
>
> portfolio=# sELeCT * FROM STOCKS LIMIT 1;
>  symbol | description | exchange
> --------+-------------+----------
>  AADBX  | AADBX       | NYSE
> (1 row)
>
> Those queries were cased differently, but were recognized as being
> functionally identical.
> --
> output = ("aa454" "@" "freenet.carleton.ca")
> http://cbbrowne.com/info/linux.html
> debugging, v:
>         Removing the needles from the haystack.

Relaxin wrote:

>No, I mean the data.
>
>select * from stocks where symbol = 'AADBX'
>and
>select * from stocks where symbol = 'aadbx'
>
>would bring up the same result set.
>
>
>
Look in the manuals, there are SQL functions like:

STRTOLOWER( ); STRTOUPPER() like those in 'C'

Usage:
-------
SELECT *
FROM stocks
WHERE
    STRTOLOWER( symbol ) = STRTOLOWER( 'AADBX' );

select * from stocks where lower(symbol) = 'aadbx';
or
select * from stocks where symbol ilike =aadbx';
or
select * from stocks where symbol ~* 'aadbx';


HTH
Darren
On Sat, 13 Sep 2003, Relaxin wrote:

> No, I mean the data.
>
> select * from stocks where symbol = 'AADBX'
> and
> select * from stocks where symbol = 'aadbx'
>
> would bring up the same result set.
>
>
> "Christopher Browne" <cbbrowne@acm.org> wrote in message
> news:m3u17gsxhe.fsf@chvatal.cbbrowne.com...
> > Quoth "Relaxin" <noname@spam.com>:
> > > Is there a way to make Postgresql case-INSENSITIVE?
> >
> > It already is.
> >
> > portfolio=# select * from stocks limit 1;
> >  symbol | description | exchange
> > --------+-------------+----------
> >  AADBX  | AADBX       | NYSE
> > (1 row)
> >
> > portfolio=# sELeCT * FROM STOCKS LIMIT 1;
> >  symbol | description | exchange
> > --------+-------------+----------
> >  AADBX  | AADBX       | NYSE
> > (1 row)
> >
> > Those queries were cased differently, but were recognized as being
> > functionally identical.
> > --
> > output = ("aa454" "@" "freenet.carleton.ca")
> > http://cbbrowne.com/info/linux.html
> > debugging, v:
> >         Removing the needles from the haystack.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
Darren Ferguson

On Sat, 13 Sep 2003, Relaxin wrote:

> No, I mean the data.
>
> select * from stocks where symbol = 'AADBX'
> and
> select * from stocks where symbol = 'aadbx'
>
> would bring up the same result set.

Potentially on some systems it'd be possible to
generate a case insensitive collation as part of a locale
and then use such for LC_COLLATE on initdb which would make all
comparisons of text fields case insensitive.  That wouldn't
let you choose some case sensitive and case insensitive
right now (until we supported different collations within
one database).  Others have already given most of the normal
query change ways already I believe (ilike, using upper or
lower, etc).

Thank you, that was the answer I was look for.

"Stephan Szabo" <sszabo@megazone.bigpanda.com> wrote in message
news:20030914172223.W53960@megazone.bigpanda.com...
>
> On Sat, 13 Sep 2003, Relaxin wrote:
>
> > No, I mean the data.
> >
> > select * from stocks where symbol = 'AADBX'
> > and
> > select * from stocks where symbol = 'aadbx'
> >
> > would bring up the same result set.
>
> Potentially on some systems it'd be possible to
> generate a case insensitive collation as part of a locale
> and then use such for LC_COLLATE on initdb which would make all
> comparisons of text fields case insensitive.  That wouldn't
> let you choose some case sensitive and case insensitive
> right now (until we supported different collations within
> one database).  Others have already given most of the normal
> query change ways already I believe (ilike, using upper or
> lower, etc).
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

Hello....
Does anyone understand what is meant by a case-insensitive database.

Are there some collations that can be changed or is there just no way to
have a true case-insensitive Postgresql database?

Thanks

"Dennis Gearon" <gearond@fireserve.net> wrote in message
news:3F64CA36.5010202@fireserve.net...
> Relaxin wrote:
>
> >No, I mean the data.
> >
> >select * from stocks where symbol = 'AADBX'
> >and
> >select * from stocks where symbol = 'aadbx'
> >
> >would bring up the same result set.
> >
> >
> >
> Look in the manuals, there are SQL functions like:
>
> STRTOLOWER( ); STRTOUPPER() like those in 'C'
>
> Usage:
> -------
> SELECT *
> FROM stocks
> WHERE
> STRTOLOWER( symbol ) = STRTOLOWER( 'AADBX' );
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Potentially on some systems it'd be possible to
> generate a case insensitive collation as part of a locale
> and then use such for LC_COLLATE on initdb which would make all
> comparisons of text fields case insensitive.

Another possibility is to create a 'case insensitive text' datatype
that has its own case-insensitive comparison operators and associated
btree opclass.  You would want to create an implicit cast to text and
an automatic cast from text so as to take advantage of the existing
text-type operations.  I'm not sure whether this would work cleanly
or would result in can't-choose-the-operator-to-use errors, but it'd
be worth trying.

            regards, tom lane

Relaxin wrote:

>Hello....
>Does anyone understand what is meant by a case-insensitive database.
>
>
You could define your own encoding, and use that.