Re: Case-sensitive problem in AS clause?

Laurent Mazuel <lmazuel@antidot.net> wrote:

> I maybe found a problem with the "isCaseSensitive" method in the
> "ResultSetMetadata" class when using it on a "AS" clause.

> I execute the query with the "AS" clause:
>> test=> Select ('Student' || "ID" ) AS StudentId, "ID", "Name"
>> from "Student" ;

> The StudentId regular identifier becomes studentid

Because a non-quoted identifier is folded to lower case in
PostgreSQL.

> but this kind of transformation is not a problem, since a regular
> identifier is not case sensitive.

Yes it is.

> the "isCaseSensitive" flag is "true". I think it is not correct

It is correct, because it will only match the lowercase name.  You
can, of course, generate that by leaving off the quotes.  It *is*
case sensitive, but there is folding of unquoted identifier names.

The easiest path is to always use lowercase identifiers.  Another
path which is not too unreasonable, especially if you have a
framework which can do it for you, is to automatically quote all
identifiers.  Using any uppercase letters in any identifiers and not
being consistent about whether or not you quote things, is sure to
cause trouble.

-Kevin