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?


Wed, 12 Mar 2003 09:03:26 -0500
mlw <pgsql@mohawksoft.com>
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



Wed, 12 Mar 2003 16:11:01 +0100
Mathieu Arnold <mat@mat.cc>
Re: 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 <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
12 Mar 2003 10:12:55 -0500
Rod Taylor <rbt@rbt.ca>
Re: 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.


Wed, 12 Mar 2003 12:57:11 -0500 (EST)
pgsql@mohawksoft.com
Re: Case insensitivity, and option?
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
12 Mar 2003 12:59:10 -0500
Rod Taylor <rbt@rbt.ca>
Re: 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, 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
>
>  
>


Wed, 12 Mar 2003 13:35:26 -0500
"Dwayne Miller" <dmiller@espgroup.net>
Re: 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, 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
12 Mar 2003 13:46:47 -0500
Rod Taylor <rbt@rbt.ca>
Re: 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


Wed, 12 Mar 2003 18:29:15 -0800
"Ron Mayer" <ron@intervideo.com>
Re: Case insensitivity, and option?