Re: Bug#108286: case sensitivity in column names
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
Import Notes
Reply to msg id not found: MessagefromKetilMaldeketil@ii.uib.noof10Aug2001100349+0200.KETIL-vk1n1589w9m.fsf@eris.bgo.nera.no
"Oliver Elphick" <olly@lfix.co.uk> writes:
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).
Yep.
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.
I've tried to pass double quotes through OleDB, but it doesn't seem to
work. On the other hand, double quotes do work with SQL
statements through (I assume) ODBC.
So to sum up,
recreated database without quotes (changing a few names that
clashed),
and
changing direct SQL queries to not quote names
did the trick (mostly).
What remains of the wishlist item is, I guess, a wish for a case sensitive
OleDB driver. Thanks for your help in clarifying things!
-kzm
--
If I haven't seen further, it is by standing in the footprints of giants
Import Notes
Reply to msg id not found: OliverElphick'smessageofFri10Aug2001112525+0100
"Oliver Elphick" <olly@lfix.co.uk> writes:
Ketil Malde wrote:
Notice how it uses mixed case column names here?
And therefore should have double-quoted them.
Or not double-quoted them --- but it has to be consistent about it.
"FooBar" and FooBar are not the same name according to the SQL standard.
So if you quote column names in the CREATE TABLE command, you have to
quote 'em forevermore. And if not, not.
regards, tom lane
Did someone say OLE DB? As I remember, there are quite a few people
looking for this. Or was it a _native_ OLE DB driver?
-Cedar
"Oliver Elphick" <olly@lfix.co.uk> writes:
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.
...
Cedar Cox <cedarc@visionforisrael.com> writes:
Did someone say OLE DB? As I remember, there are quite a few people
looking for this. Or was it a _native_ OLE DB driver?
I'm not an expert on Microsoft's rather confusing conglomerate of
database access APIs, but as I understand it (from our resident
professional), our application accesses its database through OLEDB.
The steps I used were:
1> installed the PostgreSQL ODBC driver, available from
you-know-where.
2> brought up the control panel, and added my PostgreSQL database as a
(system, I think) data source.
3> in the application's database selection, picked the driver for ODBC
sources.
Still have some problems, but at least we can read and write the
database tables.
-kzm
--
If I haven't seen further, it is by standing in the footprints of giants
Import Notes
Reply to msg id not found: CedarCox'smessageofSun12Aug2001222922+0300IDT
If your using VB6, and ADO, then the layers are as follows:
ADO
|
OLEDB (specifically "ODBC by OLDB" driver)
|
ODBC
|
PostgreSQL
Show quoted text
-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Ketil Malde
Sent: Monday, 13 August 2001 4:32 PM
To: Cedar Cox
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Re: Bug#108286: case sensitivity in column namesCedar Cox <cedarc@visionforisrael.com> writes:
Did someone say OLE DB? As I remember, there are quite a few people
looking for this. Or was it a _native_ OLE DB driver?I'm not an expert on Microsoft's rather confusing conglomerate of
database access APIs, but as I understand it (from our resident
professional), our application accesses its database through OLEDB.The steps I used were:
1> installed the PostgreSQL ODBC driver, available from
you-know-where.
2> brought up the control panel, and added my PostgreSQL database as a
(system, I think) data source.
3> in the application's database selection, picked the driver for ODBC
sources.Still have some problems, but at least we can read and write the
database tables.-kzm
--
If I haven't seen further, it is by standing in the footprints of giants---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
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.
Just a note on an easy way to do this.. run pg_dump with the no quotes
option. Then you won't have much to edit, if anything.
HTH
-Cedar