Re: Double Quoting Table Names????

Edward Muller wrote:
>
> The tables were created using phpPgAdmin30beta.
>
> Here is the SQL used to create one the tables
>
> (phpPgAdmin displays this after the create).
>
> CREATE TABLE "Clients" (
> "clientid" SERIAL,
> "name" char (40) ,
> "startdate" char (6) ,
> "enddate" char (6) ,
> "isactive" bool ,
> "acctnumber" int4 ,
> "acctpasswd" char (20) ,
> "adminid" SERIAL ,
> PRIMARY KEY ("clientid"), UNIQUE ("clientid"));
> CREATE INDEX "Clients_clientid_key" ON "Clients"("clientid");
> CREATE INDEX "Clients_name_key" ON "Clients"("name");
> CREATE INDEX "Clients_startdate_key" ON "Clients"("startdate");
> CREATE INDEX "Clients_enddate_key" ON "Clients"("enddate");
> CREATE INDEX "Clients_isactive_key" ON "Clients"("isactive");
> CREATE INDEX "Clients_acctnumber_key" ON "Clients"("acctnumber");
> CREATE INDEX "Clients_acctpasswd_key" ON "Clients"("acctpasswd");
>
> So doing the following query via the phpPgAdmin30 webpage:
>
> SELECT * from Clients;
>
> give me this error:
>
> PostgreSQL said: ERROR: Relation 'clients' does not exist
>
> Doing the same with pgsql give me:
>
> ERROR: Relation 'client' does not exist
>
> Doing the same query from JDBC give me ... well you get the idea...
>
> Now if I replace SELECT * from Clients; with SELECT * from "Clients"; it
> works fine.
>
> Why? Is it because of case?

Yes. A quoted table/column/index name is case-sensitive. An unquoted
table/column/index name is folded into lower case. If you do not
have table/column/index names with spaces, you could try the
following:

(Warning - This appears to work fine for me, but I suggest you
pg_dump your database first)

UPDATE pg_class SET relname = lower(relname);
UPDATE pg_attribute SET attname = lower(attname);

Hope that helps,

Mike Mascari
mascarm(at)mascari(dot)com