Thread: 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?
--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
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 <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
>
>
> --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.
On Wed, 2003-03-12 at 12:57, pgsql@mohawksoft.com 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 <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
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, pgsql@mohawksoft.com 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
>
>
>
> -----Original Message----- > From: Dwayne Miller [mailto:dmiller@espgroup.net] > Sent: 12 March 2003 18:35 > To: PostgreSQL Development > Subject: 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. Iirc, you can set the default collation at install time - I believe the default is case sensitive. Regards, Dave.
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, pgsql@mohawksoft.com 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 <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
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
> 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.999999% 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