Обсуждение: 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. +