Обсуждение: BUG #2764: Capital letter in tables or columns not supported

The following bug has been logged online:

Bug reference:      2764
Logged by:          Ron Teitelbaum
Email address:      ron@usmedrec.com
PostgreSQL version: 1.4.1
Operating system:   Windows
Description:        Capital letter in tables or columns not supported
Details:

I ran into a very weird problem that tured out to be a capital letter in the
table name or column name.  When trying to query a table with a capital
letter in the name I get error relation not defined.

I can get it to work with something like:
   select * from "zipCodes"

It appears to be completely case insensitive if the table is all lowercase.


I get a simular problem with capital letters in column names.  The error is
column not defined.

The table was created manually using PGAdmin.

On Fri, 2006-11-17 at 19:40 +0000, Ron Teitelbaum wrote:
> Description:        Capital letter in tables or columns not supported

Unquoted identifiers are always smashed down to lowercase. Quoted
identifiers are taken verbatim, including case.

This is not a bug: it's documented in
http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS .  Oracle has the same behavior,
butuppercase unquoted identfiers. I seem to recall reading that the SQL spec requires that identifiers be treated as
oneor the other, but doesn't say which (upper or lower).  

rkh@csb-dev=> create temp table test (lccol text, Uccol text, ALLUCCOL
text, "lccol_q" text, "Uccol_q" text, "ALLUCCOL_q" text);
CREATE TABLE
Time: 233.815 ms

rkh@csb-dev=> \d test
    Table "pg_temp_1.test"
   Column   | Type | Modifiers
------------+------+-----------
 lccol      | text |
 uccol      | text |
 alluccol   | text |
 lccol_q    | text |
 Uccol_q    | text |
 ALLUCCOL_q | text |


-rEecE (take your pick on case)

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe received SIGKILL due to catastrophic leadership. Try -uspres carter.

On 11/17/06, Ron Teitelbaum <ron@usmedrec.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      2764
> Logged by:          Ron Teitelbaum
> Email address:      ron@usmedrec.com
> PostgreSQL version: 1.4.1
> Operating system:   Windows
> Description:        Capital letter in tables or columns not supported
> Details:
>
> I ran into a very weird problem that tured out to be a capital letter in the
> table name or column name.  When trying to query a table with a capital
> letter in the name I get error relation not defined.
>
> I can get it to work with something like:
>    select * from "zipCodes"
>
> It appears to be completely case insensitive if the table is all lowercase.
>
>

that's not a bug... if you create a table or column with capital
letters postgres transform it to lowercase...

if you don't want that you need to double quotes...

create table "Foo" (Col1 int);
select * from "Foo";

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
                                       Richard Cook

Jaime Casanova wrote:
> On 11/17/06, Ron Teitelbaum <ron@usmedrec.com> wrote:
> >
> > The following bug has been logged online:
> >
> > Bug reference:      2764
> > Logged by:          Ron Teitelbaum
> > Email address:      ron@usmedrec.com
> > PostgreSQL version: 1.4.1
> > Operating system:   Windows
> > Description:        Capital letter in tables or columns not supported
> > Details:
> >
> > I ran into a very weird problem that tured out to be a capital letter in the
> > table name or column name.  When trying to query a table with a capital
> > letter in the name I get error relation not defined.
> >
> > I can get it to work with something like:
> >    select * from "zipCodes"
> >
> > It appears to be completely case insensitive if the table is all lowercase.
> >
> >
>
> that's not a bug... if you create a table or column with capital
> letters postgres transform it to lowercase...
>
> if you don't want that you need to double quotes...
>
> create table "Foo" (Col1 int);
> select * from "Foo";

I added a mention of "capitalization" in the FAQ item on this:

    <H3 id="item4.21">4.21) Why are my table and column names not
    recognized in my query?  Why is capitalization not preserved?</H3>

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +