Thread: [bug?] oddity creating table with uppercase initial

[psql (PostgreSQL) 7.2.1 and 7.3.2 on Debian testing]

Creating a table "Banks" succeeds but then appears not to exist. It does
however show in a \d listing, but won't autocomplete(!) i.e. \d Ba<tab>

On a newly created db,

=> create sequence Banks_id_seq;
CREATE
=> CREATE TABLE "Banks" ("id" integer DEFAULT nextval('"Banks_id_seq"'::text) NOT NULL);
CREATE
=> select * from banks;
ERROR:  Relation "banks" does not exist
=> select * from Banks;
ERROR:  Relation "banks" does not exist
=> CREATE TABLE "banks" ("id" integer DEFAULT nextval('"Banks_id_seq"'::text) NOT NULL);
CREATE
=> \d
         List of relations
     Name     |   Type   |  Owner
--------------+----------+----------
 Banks        | table    | ...
 banks        | table    | ...
 banks_id_seq | sequence | ...
(3 rows)

(The 7.3.2 appears to behave identically except with its listing of the
schema being public in \d)

Should I simply avoid names with uppercase? This seems like a shame.

Paul

--
Paul Makepeace ....................................... http://paulm.com/

"What is a quigibo? A yeast infection gone wrong."
   -- http://paulm.com/toys/surrealism/

SQL is case insensitive, except for the actual data.
Most commercial databases deal with this by forcing
everything to upper case (the SQL standard appears to
support this approach); PostgreSQL forces everything
to lower case.  This was a choice made long ago, and
nobody is in a hurry to change it.  Either way, you
will have a problem.

The answer is to double quote your mixed case
identifiers: select * from "Banks";

The name must have been quoted when the table was
created; if you are unaware of that, you must have
used some administration/migration tool that quoted
the names without your knowing.

Since you must remember to quote the names every time
you use them, mixed case names are a trap waiting to
spring.  My experience is that they are more trouble
than they are worth.

__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

On Mon, Jun 02, 2003 at 23:07:24 +0100,
  Paul Makepeace <postgresql.org@paulm.com> wrote:
>
> Should I simply avoid names with uppercase? This seems like a shame.

You need to use double quotes when referring to objects that have
uppercase letters in their names. Unquoted names are treated as all
lowercase by Postgres.

While you used quotes when creating the table (but not the sequence which
will case you some problems) you didn't use them in the select statement.

My recommendation would be not to really use uppercase in the table names.
You can use uppercase when referring to them to make things more readable,
but just let stuff get converted internally to lower case.

Paul Makepeace wrote:
>
> => create sequence Banks_id_seq;
> CREATE
> => CREATE TABLE "Banks" ("id" integer DEFAULT nextval('"Banks_id_seq"'::text) NOT NULL);
> CREATE
> => select * from banks;
> ERROR:  Relation "banks" does not exist

I'm new to this but I think you need to quote "Banks". Try

select * from 'Banks';

> Should I simply avoid names with uppercase? This seems like a shame.

Like the plague I should think.

It is possible to make SQL (or maybe just postgresQL) case sensitive but
I think the consensus is that it is more pain than it is worth. Someone
more versed in the in-and-outs of uppercase-lowercase issues with table
names will doubtlessly tell you all the nasty details.

HTH,

--

Jean-Christian Imbeault