Re: Column name case conversion

Zeljko Trogrlic wrote:
  >Column names are behaving very strangely. In queries, all names are
  >converted to lowercase.
  >
  >Example:
  >
  >I have a table named "LOCATION".
  >
  >select * from location;
  >I get an error message (this is OK)
  >
  >select * from LOCATION;
  >I get an error message (this is NOT OK)
  >
  >select * from "location";
  >I get an error message (this is OK)
  >
  >select * from "LOCATION";
  >Everything works (this is OK)
  >
  >Is it by design? I think that database shouldn't convert name case in SQL
  >statements, or it should try to make case-insensitive match (like
  >Interbase) if double quotes are not used.

If your table is called "LOCATION" it will only be matched by "LOCATION";
the original use of double quotes makes their use mandatory for ever
after.  SQL is case-insensitive when double quotes are not used;
your second example (select * from LOCATION) gets translated to lower-case
immediately, which is why you don't get a match on it.  This is correct
behaviour.

Best to avoid case-sensitive names altogether.


--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "He hath not dealt with us after our sins; nor rewarded
      us according to our iniquities. For as the heaven is
      high above the earth, so great is his mercy toward
      them that fear him. As far as the east is from the
      west, so far hath he removed our transgressions from
      us."     Psalms 103:10-12