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(at)mohawksoft(dot)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(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc