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

Re: Case-sensitive problem in AS clause?

From: "David Johnston" <polobo(at)yahoo(dot)com>
To: "'Dave Cramer'" <pg(at)fastcrypt(dot)com>
Cc: "'dmp'" <danap(at)ttc-cmc(dot)net>, "'Laurent Mazuel'" <lmazuel(at)antidot(dot)net>, "'PostgreSQL JDBC'" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Case-sensitive problem in AS clause?
Date: 2012-07-16 17:36:39
Message-ID: 014c01cd6379$8f14a560$ad3df020$@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
> -----Original Message-----
> From: davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] On Behalf
> Of Dave Cramer
> Sent: Monday, July 16, 2012 12:56 PM
> To: David Johnston
> Cc: dmp; Laurent Mazuel; PostgreSQL JDBC
> Subject: Re: [JDBC] Case-sensitive problem in AS clause?
> 
> The name of the column is not always case sensitive in fact postgresql all
> names are folded to lower case unless quoted
> 
> Dave Cramer
> 
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
> 
> 
> On Mon, Jul 16, 2012 at 12:49 PM, David Johnston <polobo(at)yahoo(dot)com>
> wrote:
> >> -----Original Message-----
> >> From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-
> >> owner(at)postgresql(dot)org] On Behalf Of dmp
> >> Sent: Monday, July 16, 2012 12:05 PM
> >> To: Laurent Mazuel; PostgreSQL JDBC
> >> Subject: Re: [JDBC] Case-sensitive problem in AS clause?
> >>
> >> Again as the as identifier is not quoting it default to the default
> > behavior for
> >> assignment. As far as the isCaseSensitive() it is found that the
> >> method in
> > the
> >> Pgjdbc, org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java is:
> >>
> >> /*
> >>       * Does a column's case matter? ASSUMPTION: Any field that is
> >>       * not obviously case insensitive is assumed to be case sensitive
> >>       *
> >>
> >> >
> >> > Then, the column name is changed from StudentId to studentid, but
> >> > the "isCaseSensitive" flag is "true". I think it is not correct,
> >> > since it is impossible from now, when a user ask for the StudentId
> >> > column to retrieve it from a resultset. The "isCaseSensitive" don't
> >> > authorize to accept the string StudentId as a valid column name for
> >> > the studentid column in the ResultSet.
> >>
> >> Recommed you quote AS "StudentID" to get the behavior you desire.
> >>
> >> danap.
> >
> > Does the "isCaseSensitive(int)" function return whether the "value"
> > stored in the column is case-sensitive or does it return whether the
> > "name" of the column is case-sensitive?
> >
> > The OP is using it to determine whether the "name" is case-sensitive -
> > which it is always.
> >
> > My assumption is that it would indicate whether the "value" is
> > case-sensitive - which is also true because varchar/text is
case-sensitive.
> >
> > The fact that different fields may or may not be case-sensitive
> > supports this since the "case-sensitive" property of a column "name"
> > should be constant throughout the database/product.
> >
> > David J.
> >

Folding to lower-case does not negate the fact that the name itself is
always case-sensitive.  It is just when you don't put the name in quotes a
pre-processing phase translates the name into lower-case.  I get that this
is semantics but in a true case-insensitive system it would not matter
whether you used quotes or not when providing the name.  So in PostgreSQL
names are always case-sensitive but if the name is not quoted the case of
the name is automatically converted to lower-case before the case-sensitive
comparison is made.  In other words - once the name is stored it no longer
matters how the value was entered (i.e., with or without qutotes) its value
is stored in a case-sensitive manner.  There is no way to store an
mixed-case value and then compare to it in a case-insensitive manner.  E.G.,
If my field name has been stored as "ColumnA" there is no way to reference
that column other than by writing: SELECT "ColumnA" ... Futhermore the
system cannot distinguish between (columna) AND ("columna") - both
representations are equivalent.  By your logic the former should return
"case-insensitive" while the later should return "case-sensitive".  

David J.





In response to

pgsql-jdbc by date

Next:From: dmpDate: 2012-07-16 18:31:23
Subject: Re: Case-sensitive problem in AS clause?
Previous:From: Radim KolarDate: 2012-07-16 17:21:26
Subject: not fetching all query results

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