case sensitivity in column names

Package: postgresql

Hi,

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

I'm not sure this deserves status as a bug, but it is a real problem
for me. Trying to access Postgres from a program using OleDB through
the Postgres ODBC driver, and Microsoft's OleDB driver for ODBC
drivers.

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.

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.

Apparently, I can type case sensitive column names in psql by
enclosing them in double quotes, but I haven't found a way to pass
that through ODBC yet.

I've searched the mail archives on postgresql.org, to little avail.

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:

> ERROR from backend during send_query: 'ERROR: Relation 'faultattributes' does not have attribute 'agent_name''
> STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while executing the query'
> ------------------------------------------------------------
> hdbc=77170008, stmt=71781856, result=71781648
> manual_result=0, prepare=1, internal=0
> bindings=0, bindings_allocated=0
> parameters=71780896, parameters_allocated=7
> statement_type=1, statement='INSERT INTO FaultAttributes ( Agent_Name,Avail,LogFAct,MLogS,AThresh,WrapState,AThreshState) VALUES(?,?,?,?,?,?,?)'
> stmt_with_params='INSERT INTO FaultAttributes ( Agent_Name,Avail,LogFAct,MLogS,AThresh,WrapState,AThreshState) VALUES('P005756','1'::numeric,'1'::numeric,'10000'::numeric,'80'::numeric,'0'::numeric,'0'::numeric)'

Notice how it uses mixed case column names here? Anyway, more junk
from the same:

> data_at_exec=-1, current_exec_param=-1, put_data=0
> currTuple=-1, current_col=-1, lobj_fd=-1
> maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
> cursor_name='SQL_CUR04474DE0'
> ----------------QResult Info -------------------------------
> fields=71781616, manual_tuples=0, backend_tuples=0, tupleField=0, conn=0
> fetch_count=0, fcount=0, num_fields=0, cursor='(NULL)'
> message='(NULL)', command='(NULL)', notice='(NULL)'
> status=7, inTuples=0
>CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: Relation 'faultattributes' does not have attribute 'agent_name''
> ------------------------------------------------------------
> henv=71762848, conn=77170008, status=1, num_stmts=16
> sock=71777936, stmts=71777856, lobj_type=-999
> ---------------- Socket Info -------------------------------
> socket=260, reverse=0, errornumber=0, errormsg='(NULL)'
> buffer_in=77176496, buffer_out=77180616
> buffer_filled_in=74, buffer_filled_out=0, buffer_read_in=74

And now from /var/log/postgresql.log:

> 2001-08-07 10:45:11 [22006] ERROR: Relation 'faultattributes' does not have attribute 'agent_name'

Here the query is lower-cased!

-kzm
--
If I haven't seen further, it is by standing in the footprints of giants