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@postgreSQL.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@d168.paris-228.cybercable.fr 
[212.198.228.168])
	by hub.org (8.9.3/8.9.3) with ESMTP id NAA39566
	for <pgsql-interfaces@postgresql.org>; Sun, 6 Feb 2000 13:37:49 -0500 
(EST)
	(envelope-from charpent@bacbuc.dyndns.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@beth.bacbuc.fdn.fr> (Debian Smail3.2.0.101)
	for <pgsql-interfaces@postgresql.org>; Sun, 6 Feb 2000 19:37:48 +0100 
(CET) 
From: Emmanuel Charpentier <charpent@bacbuc.dyndns.org>
Date: Sun, 06 Feb 2000 18:37:59 GMT
Message-ID: <20000206.18375900@localhost.localdomain>
Subject: Case sensitivity in ODBC ??
To: pgsql-interfaces@postgresql.org
Reply-To: charpent@bacbuc.dyndns.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








Sun, 06 Feb 2000 22:51:34 GMT
Emmanuel Charpentier <charpent@bacbuc.dyndns.org>
Case sensitivity issues
Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:
> [ much snipped ]
> 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 ?

It's hard to tell what your driver is doing, but the underlying backend
behavior is simple enough.

A table or field name written in an SQL query is forced to lowercase
*unless* it is written with double-quotes around it:

	SELECT * FROM Table;           -- refers to "table"

	SELECT * FROM "Table";         -- refers to "Table"

Your debugging output shows that the CREATE TABLE statement is being
sent as-is, so the name is lowercased before the CREATE happens.
You didn't show what was being sent for your other queries like
sqlColumns().  I speculate that the driver is translating those into
SQL queries in which the provided name is quoted...

			regards, tom lane

Sun, 06 Feb 2000 21:29:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Case sensitivity issues 
On Sun, Feb 06, 2000 at 10:51:34PM +0000, Emmanuel Charpentier wrote:
> I have lived that SQL database were not to be case-sensitive. However, 

Which one? I have yet to work with one, that is unless you count Access as a
real database which it is not.

> This interface mostly works, but has some odd behaviour :
> /* Querying ï¿½ usarrests ï¿½ does ! Note that column names have been 
> lowercased as well ... */

Sure. SQL is case insesitive by default. Quote your case-sensitive string
with double-quotes and it works.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

Mon, 7 Feb 2000 07:48:53 +0100
Michael Meskes <meskes@postgresql.org>
Re: [HACKERS] Case sensitivity issues