Re: Bug#108286: case sensitivity in column names - Mailing list pgsql-odbc

Ketil Malde wrote:

  >[NB! This is a Debian bug report, but Cc:'ed to the PostgreSQL lists.
  >Please make sure any replies are sent to

108286@bugs.debian.org

  >Works, in a way, data can be inserted and extracted from tables, but
  >there is one problem:  Queries are case sensitive, and it appears that
  >PostgreSQL automatically lower-case the column names in queries before
  >running them - even though the columns have mixed-case names.

Correct.  SQL standards say that names are case-insensitive unless
quoted.  Microsoft is not known for its adherence to standards...

  >I'm not sure why, I mean, one would think that either the DB is case
  >insensitive, in which case querying with lower case should work; or
  >alternatively, it is case sensitive, in which case it shouldn't change
  >case in queries.

You must have created the table in PostgreSQL using quoted names for
the column (but not for the table, since PostgreSQL matches
FaultAttributes with faultattributes).

Why not dump and recreate your database, but edit the dumpfile to
remove the double-quotes and thus force the names to become
case-insensitive?  Then it won't matter that OleDB is sending garbage.

  >Here's stuff from the logs, just in case anybody's interested, first
  >from the ODBC-driver log on the windows box where the client runs:


  >>                 statement_type=1, statement='INSERT INTO FaultAttributes (
      > Agent_Name,Avail,LogFAct,MLogS,AThresh,WrapState,AThreshState) VALUES(?
      >,?,?,?,?,?,?)'

  >Notice how it uses mixed case column names here?

And therefore should have double-quoted them.  This is a bug in whatever
program is generating the query.

  >And now from /var/log/postgresql.log:
  >
  >> 2001-08-07 10:45:11 [22006]  ERROR:  Relation 'faultattributes' does not h
      >ave attribute 'agent_name'
  >
  >Here the query is lower-cased!

Correctly.

The bottom line is, that capitalising words in names is not a good
idea in SQL.  I don't think this is a bug in PostgreSQL at all.  It
is working according to spec.  Bug downgraded to wishlist and I will
close it soon unless I see something to convince me that it is a bug
after all.

Oliver Elphick
Debian maintainer

--
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
                 ========================================
     "If ye abide in me, and my words abide in you, ye shall
      ask what ye will, and it shall be done unto you."
                                      John 15:7