Re: Cold Fusion to pgSQL

On Fri, Jul 06, 2001 at 12:49:16PM -0700, Daniel Clark wrote:
> Hi all,
>
> A very newbie question...
>
> Just started using ColdFusion5.0(Linux) against pgSQL(Linux7.1) using ODBC
>
> It seems that ColdFusion sends along LowerCase TableNames and ColumnNames to pgSQL even if I program the .cfm files with MixedCase strings
>
> The result is failure to locate tables and columns until I rename them in all lowercase.
>
> Is there some feature in pgSQL that can make it insensitive to case in tablenames and column names?
>

This is a required feature of ANSI SQL: case insensitivity. PostgreSQL
implements this by downcasing all identifiers (Oracle, and MS-SQL,
UPCASE EVERYTHING), unless they're quoted. I'm guessing your using some
tool to create your MixedCase tablenames, and it's quoting them for you
behind your back.

So, either lose the quotes on the identifiers when you create the schema
(i.e., let them get down cased) or write all your CF like so:

select "SomeColumn"."SomeTable", "SomeOtherColumn"."SomeOtherTable" from
"SomeTable","SomeOtherTable" ...

Ross

P.S. Yup, you have to quote each identifier seperately, not the dotted
identifier. Because, according to the standard, once you quote it,
anything goes, so "This.is my Really Weird_column.name" is completely
valid.