case-insensitive database

[Relaxin]

Is there a way to make Postgresql case-INSENSITIVE?

Thanks

[Christopher Browne]

Quoth "Relaxin" <no****@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.

[Christopher Browne]

Quoth "Relaxin" <no****@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]

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" <cb******@acm.org> wrote in message
news:m3************@chvatal.cbbrowne.com...

Quoth "Relaxin" <no****@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]

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" <cb******@acm.org> wrote in message
news:m3************@chvatal.cbbrowne.com...

Quoth "Relaxin" <no****@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.

[Dennis Gearon]

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

[Relaxin]

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" <ge*****@fireserve.net> wrote in message
news:3F**************@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]

On Sat, 13 Sep 2003, Relaxin wrote:

Is there a way to make Postgresql case-INSENSITIVE?

Case insensitive in what regard?
That textual comparisons are done case-insensitively in queries?
That object names are determined case-insensitively?
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[darren]

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" <cb******@acm.org> wrote in message
news:m3************@chvatal.cbbrowne.com...

Quoth "Relaxin" <no****@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 ma*******@postgresql.org

--
Darren Ferguson
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Stephan Szabo]

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Relaxin]

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

"Stephan Szabo" <ss****@megazone.bigpanda.com> wrote in message
news:20*******************@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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Stephan Szabo <ss****@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Dennis Gearon]

Relaxin wrote:

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

You could define your own encoding, and use that.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org