Case sensitivity issues

It seems that PostgreSQKL might have a case-sensitivity problem.

Sorry to post that here. I tried to post on pgsql-interfaces, and my
post has bounced (for no reason I have been able to fathom : the
subject line of the bounce messageb is : BOUNCE
pgsql-interfaces(at)postgreSQL(dot)org: Admin request of type /^\s*config\b/i
at line 7. Beats me ...). I searched on the web site a reference to a
list manager, didn't find it ...

Feel free to flame if this is not the correct procedure ... :-)).

Emmanuel Charpentier

Bounced post follows :

>From bouncefilter Sun Feb 6 13:38:25 2000
Received: from beth.bacbuc.fdn.fr (root(at)d168(dot)paris-228(dot)cybercable(dot)fr
[212.198.228.168])
by hub.org (8.9.3/8.9.3) with ESMTP id NAA39566
for <pgsql-interfaces(at)postgresql(dot)org>; Sun, 6 Feb 2000 13:37:49 -0500
(EST)
(envelope-from charpent(at)bacbuc(dot)dyndns(dot)org)
Received: from localhost.localdomain (really [193.57.55.1]) by
bacbuc.fdn.fr
via in.smtpd with smtp (ident charpent using rfc1413)
id <m12HWZA-000QgVC(at)beth(dot)bacbuc(dot)fdn(dot)fr> (Debian Smail3.2.0.101)
for <pgsql-interfaces(at)postgresql(dot)org>; Sun, 6 Feb 2000 19:37:48 +0100
(CET)
From: Emmanuel Charpentier <charpent(at)bacbuc(dot)dyndns(dot)org>
Date: Sun, 06 Feb 2000 18:37:59 GMT
Message-ID: <20000206(dot)18375900(at)localhost(dot)localdomain>
Subject: Case sensitivity in ODBC ??
To: pgsql-interfaces(at)postgresql(dot)org
Reply-To: charpent(at)bacbuc(dot)dyndns(dot)org
X-Mailer: Mozilla/3.0 (compatible; StarOffice/5.1; Linux)
X-Priority: 3 (Normal)
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id
NAA39635

Dear pgsql-interface list,

I have lived that SQL database were not to be case-sensitive. However,
it seems that, at least through the ODBC interface, PostgreSQL 6.5.3
is :

Config : Linux RedHat 6.1, PostgreSQL 6.5.3 from the PostgreSQL ftp
site's RPM, unixODBC1.7 (including their PostgreSQL driver). Works
great from various interfaces (including StarOffice ...).

I'm working from the R statistical language/package through a beta
ODBC interface. Note that the interpreted R language is case-sensitive
...

Logical Setup

R user interface ---> RODBC library ---> unixODBC ---> PostgreSQL
^
|
I'm trying to enhance that---+

This interface mostly works, but has some odd behaviour :

> sqlQuery(chan1,"create table Test1 (id int4 not null primary key, val
varchar(255))") /* This creates the table test1, correctly */

> sqlColumns(chan1,"test1") /* Sanity check */
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE
TYPE_NAME
1 NA NA test1 id 4
int4
2 NA NA test1 val 12
varchar
PRECISION LENGTH SCALE RADIX NULLABLE REMARKS DISPLAY_SIZE
FIELD_TYPE
1 10 4 0 10 0 NA 11
23
2 254 254 NA NA 1 NA 254
1043
/* This is the expected answer */

> sqlTables(chan1)
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS
1 NA NA pga_forms TABLE NA
2 NA NA pga_layout TABLE NA
3 NA NA pga_queries TABLE NA
4 NA NA pga_reports TABLE NA
5 NA NA pga_schema TABLE NA
6 NA NA pga_scripts TABLE NA
7 NA NA test1 TABLE NA
/* This also is the expected answer. Note however that the name of the
table is lowercased */

> sqlColumns(chan1,"Test1") /* Same sanity check, with the original name
*/
Error in sqlColumns(chan1, "Test1") : Test1 :table not found on
channel 0
/* This is unexpected if SQL, PostgreSQL and ODBC are case-insensitive
*/

Furthermore : debugging shows that the initial request for table
creation seds the name with its capital, as shown in the next example
:

> sqlSave(chan1,USArrests,rownames="State") /* The sqlSave function
creates the necessary table if it does not exists */
[1] "CREATE TABLE USArrests (State varchar(255) ,Murder varchar(255)
,Assault varchar(255) ,UrbanPop varchar(255) ,Rape varchar(255) )"
/* This is a debugging output showing the exact request sent to
PosstgreSQL, minus the ending semicolumn. Yes the types are
ridiculous, and that's what I'm trying to fix ... */
Error in sqlColumns(channel, tablename) : USArrests :table not found
on channel 0
/* This is an (unexpected) error message*/
> sqlTables(chan1)
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS
1 NA NA pga_forms TABLE NA
2 NA NA pga_layout TABLE NA
3 NA NA pga_queries TABLE NA
4 NA NA pga_reports TABLE NA
5 NA NA pga_schema TABLE NA
6 NA NA pga_scripts TABLE NA
7 NA NA usarrests TABLE NA
/* A table « usarrests » has been created, but querying « USArrests »
does not work. However :*/

> sqlColumns(chan1,"usarrests")
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE
TYPE_NAME
1 NA NA usarrests state 12
varchar
2 NA NA usarrests murder 12
varchar
3 NA NA usarrests assault 12
varchar
4 NA NA usarrests urbanpop 12
varchar
5 NA NA usarrests rape 12
varchar
PRECISION LENGTH SCALE RADIX NULLABLE REMARKS DISPLAY_SIZE
FIELD_TYPE
1 254 254 NA NA 1 NA 254
1043
2 254 254 NA NA 1 NA 254
1043
3 254 254 NA NA 1 NA 254
1043
4 254 254 NA NA 1 NA 254
1043
5 254 254 NA NA 1 NA 254
1043
>
/* Querying « usarrests » does ! Note that column names have been
lowercased as well ... */

This seems to me proof enough that the behaviour of PostgreSQL (or
ODBC driver) related to case sensitivity is not coherent.

Could some kind soul shed some light on this ?

Thanks in advance,

Emmanuel Charpentier