Re: PG case sensitivity

On Tue, 2004-09-14 at 12:37, Christian Sell wrote:
> Hello,
>
> I am running into a problem with PGs case sensitivity with regard to column and
> table names. I am using program components that require the object names
> returned from database metadata queries to be in uppercase. Therefore, I am
> forced to use double quotes in the table creation scripts, like

Why do programs that interface with case-insensitive SQL require a
particular case?

> create table "BLA" ();
>
> However, after doing that, all scripts that reference objects without quotes
> fail, as PG seems to internally translate to lowercase in the absence of
> quotes. I am forced to touch each and every column and table name in every
> script. Questions:
>
> 1) can PG be configured to operate case insensitive?

It does, but in lower case (which is a good deal more readable).  Using
lower rather than upper case is a decision made many years ago.

> 2) why in the world was case sensitivity introduced at all? AFAIK, the SQL
> standard explicitly states that names are case insensitive, and it seems to me
> that PG goes against that standard.

It doesn't.  It simply folds to lower case rather than upper case.  If
things are truly case-insensitive, this should be of no consequence.

The fault is with your program components that are insisting on upper
case rather than accepting either case.  Perhaps you need some
intermediate component to swap case on identifiers for their benefit.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "But without faith it is impossible to please him; for
      he that cometh to God must believe that he is, and
      that he is a rewarder of them that diligently seek
      him."        Hebrews 11:6