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

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:

-----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