Re: [HACKERS] Case insensitivity, and option?
select * from table where field ILIKE 'blAH'; -- ;-)
is almost as easy :-)
PS: no, don't do this if you want portability. I think the charset
idea's a better one.
Ron
select * from table where lower(field)=lower('BLah') will break
portability too in the sense that many DBs (perhaps all commercial
ones) do not support functional indexes. Hence
lower(field)=lower('BLah') query will not be using index when it runs
on those DBs.
Besides, the developer must always remember to use conversion when
writing queries. This does not sound bad initially but in a big
application with many developers and complex query logic this will
create quite a few annoying, time consuming and difficult to find bugs.
Case insensitive charset definitely sounds like a better idea. Has
anyone done this yet? IMHO, this should be a part of core distribution
since 99.99% of queries do not need case sensitivity.
Thanks
__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] Case insensitivity, and option?
I was at a client office reviewing some code. They use MSSQL and I noticed that: select * from table where field = 'blah'; gave the same results as: select * from table where field = 'BLah'; I was shocked. (a) because I know a lot of my code could be easier to write, and (b) that their code would break on every other database I am aware of. Does anyone know about this? Is it practical/desirable for PostgreSQL to have this as a configuration setting? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Case insensitivity, and option?
--le 12/03/2003 09:03 -0500, mlw écrivait :
| I was at a client office reviewing some code. They use MSSQL and I
| noticed that:
|
| select * from table where field = 'blah';
| gave the same results as:
| select * from table where field = 'BLah';
|
| I was shocked. (a) because I know a lot of my code could be easier to
| write, and (b) that their code would break on every other database I am
| aware of. Does anyone know about this?
|
| Is it practical/desirable for PostgreSQL to have this as a configuration
| setting?
Well, I quite don't see any difference with writing :
select * from table where lower(field) = lower('BLah');
--
Mathieu Arnold
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Case insensitivity, and option?
On Wed, 2003-03-12 at 09:03, mlw wrote: I was at a client office reviewing some code. They use MSSQL and I noticed that: select * from table where field = 'blah'; gave the same results as: select * from table where field = 'BLah'; I was shocked. (a) because I know a lot of my code could be easier to write, and (b) that their code would break on every other database I am aware of. Does anyone know about this? Same thing with MySQL. It's a royal pain in the ass. It makes using non-ascii (unicode for example) text near to impossible because of this. Is it practical/desirable for PostgreSQL to have this as a configuration setting? I think we already support this. Create a new character set with upper / lower case specified as being equal and PostgreSQL should behave as expected. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Case insensitivity, and option?
--le 12/03/2003 09:03 -0500, mlw écrivait :
| I was at a client office reviewing some code. They use MSSQL and I |
noticed that:
|
| select * from table where field = 'blah';
| gave the same results as:
| select * from table where field = 'BLah';
|
| I was shocked. (a) because I know a lot of my code could be easier to
| write, and (b) that their code would break on every other database I
am | aware of. Does anyone know about this?
|
| Is it practical/desirable for PostgreSQL to have this as a
configuration | setting?
Well, I quite don't see any difference with writing :
select * from table where lower(field) = lower('BLah');
That would probably require an extra index, especially if 'field' is a
primary key.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Case insensitivity, and option?
On Wed, 2003-03-12 at 12:57, [EMAIL PROTECTED] wrote:
--le 12/03/2003 09:03 -0500, mlw écrivait :
| I was at a client office reviewing some code. They use MSSQL and I |
noticed that:
|
| select * from table where field = 'blah';
| gave the same results as:
| select * from table where field = 'BLah';
|
| I was shocked. (a) because I know a lot of my code could be easier to
| write, and (b) that their code would break on every other database I
am | aware of. Does anyone know about this?
|
| Is it practical/desirable for PostgreSQL to have this as a
configuration | setting?
Well, I quite don't see any difference with writing :
select * from table where lower(field) = lower('BLah');
That would probably require an extra index, especially if 'field' is a
primary key.
I don't know about MSSql, but on MySQL you also require a different
index for a case sensitive comparison. Problem is, they don't (didn't)
support functional indexes -- so you simply couldn't make one.
End up with: WHERE field = 'var' and strcasecmp(field, 'var') everywhere
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Case insensitivity, and option?
I know that the MSSQL code works because the default collation sequence
for character fields is case-insensitive. You can change it for each
field independantly to be case sensitive, local specific, etc. I'm not
sure if PG supports a collation sequence attribute on column
definitions/indexes.
Rod Taylor wrote:
On Wed, 2003-03-12 at 12:57, [EMAIL PROTECTED] wrote:
--le 12/03/2003 09:03 -0500, mlw écrivait :
| I was at a client office reviewing some code. They use MSSQL and I |
noticed that:
|
| select * from table where field = 'blah';
| gave the same results as:
| select * from table where field = 'BLah';
|
| I was shocked. (a) because I know a lot of my code could be easier to
| write, and (b) that their code would break on every other database I
am | aware of. Does anyone know about this?
|
| Is it practical/desirable for PostgreSQL to have this as a
configuration | setting?
Well, I quite don't see any difference with writing :
select * from table where lower(field) = lower('BLah');
That would probably require an extra index, especially if 'field' is a
primary key.
I don't know about MSSql, but on MySQL you also require a different
index for a case sensitive comparison. Problem is, they don't (didn't)
support functional indexes -- so you simply couldn't make one.
End up with: WHERE field = 'var' and strcasecmp(field, 'var') everywhere
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Case insensitivity, and option?
On Wed, 2003-03-12 at 13:35, Dwayne Miller wrote:
I know that the MSSQL code works because the default collation sequence
for character fields is case-insensitive. You can change it for each
field independantly to be case sensitive, local specific, etc. I'm not
sure if PG supports a collation sequence attribute on column
definitions/indexes.
Seems to, but it's on a database level -- not per column / index.
In other-words, you could potentially make the entire database case
insensitive.
I've not tried this myself, but there are people on the list who could
answer this definitively.
Rod Taylor wrote:
On Wed, 2003-03-12 at 12:57, [EMAIL PROTECTED] wrote:
--le 12/03/2003 09:03 -0500, mlw écrivait :
| I was at a client office reviewing some code. They use MSSQL and I |
noticed that:
|
| select * from table where field = 'blah';
| gave the same results as:
| select * from table where field = 'BLah';
|
| I was shocked. (a) because I know a lot of my code could be easier to
| write, and (b) that their code would break on every other database I
am | aware of. Does anyone know about this?
|
| Is it practical/desirable for PostgreSQL to have this as a
configuration | setting?
Well, I quite don't see any difference with writing :
select * from table where lower(field) = lower('BLah');
That would probably require an extra index, especially if 'field' is a
primary key.
I don't know about MSSql, but on MySQL you also require a different
index for a case sensitive comparison. Problem is, they don't (didn't)
support functional indexes -- so you simply couldn't make one.
End up with: WHERE field = 'var' and strcasecmp(field, 'var') everywhere
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Case insensitivity, and option?
mlw wrote: ... select * from table where field = 'blah'; gave the same results as: select * from table where field = 'BLah'; I was shocked. (a) because I know a lot of my code could be easier to write ... select * from table where field ILIKE 'blAH'; -- ;-) is almost as easy :-) PS: no, don't do this if you want portability. I think the charset idea's a better one. Ron ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
