RE: Case sensitivity - Mailing list pgsql-odbc

Hello,
    I personally make all my names lower case to try and avoid such
conflicts. All unquoted names become lower case. the query would of course
work like this:
    select * from "Logins" where "Logins"."Login"='rhurst'
or if you'd used lower case for tables and field names this:
    select * from LoGiNs where "logins"."login"='rhurst'

    When I was converting a file based Access DB (which uses case
insensitivity) and had table and field names that where capitilized
(actually some of the field names where truly grotty with commas and slahes
in so I had to do something) I used Access queries as views to linekd tables
so that the Access DB would refer to (I created a little routine to automate
this as I had some wide tables (as in lots of fields)) and made tables in pg
all in lower case. Could you do something similar here? (If you used PG
Views the first and second relation reference would have to agree still,
which is the problem here).
    I'd guess that any case insensitivity would have to be in the back
end (comparing lower(name) rather than normal). I have no idea how hard this
would be, or even wether it would be a particularly good idea (would quoted
names still be case sensitive for instance?). I guess it might be possible
to have in the parse option of ODBC something that always put a relation
name through a query to find a case insensitive name then change the
relationname to what ever was returned. I don't know quite how good the
parse option is though and that would also mean an extra query. I'd guess
it'd also be wanted to cover field names as well. This would mean then
querying about them as well.
    A quick aside: for case insensitive searches to be efficient you
have to set up a suitable index (eg on lower(login) ) and then make sure it
gets used, I must admit I'm not sure when such and index would and wouldn't
be used (is it used for ilike for instance, or only when specifically the
lower function is used?).
Hmm, I seem to be rambling a bit this morning
- Stuart

    -----Original Message-----
    From:    Robert Hurst [SMTP:r_hurst@butler.org]
    Sent:    Monday, May 14, 2001 1:35 PM
    To:    pgsql-odbc@postgresql.org
    Cc:    Jean-Michel POURE
    Subject:    RE: Case sensitivity

    Oh, I must not have been too clear in the problem/solution I was
seeking.
    First, I did not know about the ILIKE conditional, thanks.  But, I
was not
    looking for case insensitivity with literals.

        select * from logins where "Logins"."Login"='rhurst'

    The 'logins' in FROM is not the same as 'Logins' in WHERE.  And,
PostgreSQL
    does not validate all the relations in FROM to match up with what is
used in
    SELECT / WHERE, so this SQL call is accepted.  Unless I use a hex
editor on
    the client binary (which isn't a bad idea), I was just suggesting
that a
    case insensitivity option on the PostgreSQL server and/or the ODBC
client
    would be choice.  Or even if one exists now?

    TIA!


    > -----Original Message-----
    > From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
    > Sent: Saturday, May 12, 2001 3:55am
    > To: Robert Hurst
    > Subject: Re: [ODBC] Case sensitivity
    >
    >
    > Hello Robert ,
    >
    > You should try
    > select * from logins where Login ilike ='rhurst'
    >
    > LIKE statement is case sensible,
    > ILIKE is not case sensible
    >
    > For more information, visit
    > http://odbc.postgresql.org/docs/index.php?functions-matching.html
    >
    > Greetings from Jean-Michel POURE, Paris, France
    >
    > At 18:41 08/05/01 -0400, you wrote:
    > >A suggestion for a feature to add to your already wonderful ODBC
    > capability.
    > >Could you add a boolean option that enables/disables case
    > sensitivity with
    > >ODBC calls?  Consider this example, a Delphi client sends:
    > >
    > >         select * from logins where "Logins"."Login"='rhurst'
    > >
    > >I know it looks ridiculous.  This currently has the ill effect of
forcing
    > >pgsql to look for the table 'logins' (case sensitive) when only
'Logins'
    > >exist!  Wouldn't it be nice if I could set an ignore-case flag
for ODBC?
    > >
    > >
    > >Robert Hurst, I.S. Director
    > >Butler Hospital · 345 Blackstone Boulevard · Providence, RI 02906
    > >Voice: (401) 455-6262 · Pager: (401) 460-4594
    > >A Care New England hospital, http://www.carenewengland.org
    > >"Any technology distinguishable from magic is insufficiently
advanced."
    > >
    > >
    > >---------------------------(end of
broadcast)---------------------------
    > >TIP 5: Have you checked our extensive FAQ?
    > >
    > >http://www.postgresql.org/users-lounge/docs/faq.html
    >