By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

case-insensitive database

P: n/a
Is there a way to make Postgresql case-INSENSITIVE?

Thanks
Nov 11 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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.
Nov 11 '05 #2

P: n/a
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.
Nov 11 '05 #3

P: n/a
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.

Nov 11 '05 #4

P: n/a
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.

Nov 11 '05 #5

P: n/a
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

Nov 11 '05 #6

P: n/a
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

Nov 11 '05 #7

P: n/a

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

Nov 11 '05 #8

P: n/a
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

Nov 11 '05 #9

P: n/a

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

Nov 11 '05 #10

P: n/a
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

Nov 11 '05 #11

P: n/a
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

Nov 11 '05 #12

P: n/a
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

Nov 11 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.