Skip site navigation (1) Skip section navigation (2)

Re: Bug#108286: case sensitivity in column names

From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: Ketil Malde <ketil(at)ii(dot)uib(dot)no>, 108286(at)bugs(dot)debian(dot)org
Cc: pgsql-general(at)postgresql(dot)org, pgsql-odbc(at)postgresql(dot)org
Subject: Re: Bug#108286: case sensitivity in column names
Date: 2001-08-10 10:25:25
Message-ID: 200108101025.f7AAPPYx012500@linda.lfix.co.uk (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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(at)bugs(dot)debian(dot)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(dot)Elphick(at)lfix(dot)co(dot)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 



Responses

pgsql-odbc by date

Next:From: Tito DuarteDate: 2001-08-10 10:50:54
Subject: ODBC Connection with winword 2000
Previous:From: Ketil MaldeDate: 2001-08-10 08:03:49
Subject: case sensitivity in column names

pgsql-general by date

Next:From: Paulo JanDate: 2001-08-10 11:43:47
Subject: Subselects running out of memory
Previous:From: Steve SAUTETNERDate: 2001-08-10 08:45:45
Subject: installing Procedural Language PL/PGSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group