Thread: Double Quoting Table Names????

Double Quoting Table Names????

From
Edward Muller
Date:
I am using a Postgresql 7.1.2 server and it seems that I need to put ""
around my table name when doing select queries either using JDBC, PHP or
the psql interface.

None of the docs that I've looked at indicate that I should need to do
this.

Can anyone explain this?


Re: Double Quoting Table Names????

From
wsheldah@lexmark.com
Date:

Does your table name use capital letters, spaces, or SQL reserved words?  What's
the name?



Edward Muller <edwardam%home.com@interlock.lexmark.com> on 09/26/2001 12:58:01
PM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] Double Quoting Table Names????


I am using a Postgresql 7.1.2 server and it seems that I need to put ""
around my table name when doing select queries either using JDBC, PHP or
the psql interface.

None of the docs that I've looked at indicate that I should need to do
this.

Can anyone explain this?


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org





Re: Double Quoting Table Names????

From
"Nick Fankhauser"
Date:
I can't explain it, but I can add the information that I have not had to do
this with either psql or JDBC running on the same version of the backend, so
I expect this is something unusual about your particular environment or
installation.

-Nick

> am using a Postgresql 7.1.2 server and it seems that I need to put ""
> around my table name when doing select queries either using JDBC, PHP or
> the psql interface.
>
> None of the docs that I've looked at indicate that I should need to do
> this.
>
> Can anyone explain this?


Re: Double Quoting Table Names????

From
Mike Mascari
Date:
Edward Muller wrote:
>
> I am using a Postgresql 7.1.2 server and it seems that I need to put ""
> around my table name when doing select queries either using JDBC, PHP or
> the psql interface.
>
> None of the docs that I've looked at indicate that I should need to do
> this.
>
> Can anyone explain this?

How were these tables created? A common cause of this condition is
that Access users use the Export utility to create the tables via
the ODBC driver, and Access issues a CREATE TABLE statement with the
table and column names quoted.

Mike Mascari
mascarm@mascari.com

Re: Double Quoting Table Names????

From
wsheldah@lexmark.com
Date:

Good point.  I think that SQL Server does this as well.  Reason is to preserve
any spaces or capital letters in the table names.  Ugh!

Wes



Mike Mascari <mascarm%mascari.com@interlock.lexmark.com> on 09/26/2001 01:39:00
PM

To:   Edward Muller <edwardam%home.com@interlock.lexmark.com>
cc:   pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
      Sheldahl/Lex/Lexmark)
Subject:  Re: [GENERAL] Double Quoting Table Names????


Edward Muller wrote:
>
> I am using a Postgresql 7.1.2 server and it seems that I need to put ""
> around my table name when doing select queries either using JDBC, PHP or
> the psql interface.
>
> None of the docs that I've looked at indicate that I should need to do
> this.
>
> Can anyone explain this?

How were these tables created? A common cause of this condition is
that Access users use the Export utility to create the tables via
the ODBC driver, and Access issues a CREATE TABLE statement with the
table and column names quoted.

Mike Mascari
mascarm@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org





Re: Double Quoting Table Names????

From
Edward Muller
Date:
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?


On Wed, 2001-09-26 at 13:29, wsheldah@lexmark.com wrote:
>
>
> Good point.  I think that SQL Server does this as well.  Reason is to preserve
> any spaces or capital letters in the table names.  Ugh!
>
> Wes
>
>
>
> Mike Mascari <mascarm%mascari.com@interlock.lexmark.com> on 09/26/2001 01:39:00
> PM
>
> To:   Edward Muller <edwardam%home.com@interlock.lexmark.com>
> cc:   pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
>       Sheldahl/Lex/Lexmark)
> Subject:  Re: [GENERAL] Double Quoting Table Names????
>
>
> Edward Muller wrote:
> >
> > I am using a Postgresql 7.1.2 server and it seems that I need to put ""
> > around my table name when doing select queries either using JDBC, PHP or
> > the psql interface.
> >
> > None of the docs that I've looked at indicate that I should need to do
> > this.
> >
> > Can anyone explain this?
>
> How were these tables created? A common cause of this condition is
> that Access users use the Export utility to create the tables via
> the ODBC driver, and Access issues a CREATE TABLE statement with the
> table and column names quoted.
>
> Mike Mascari
> mascarm@mascari.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



Re: Double Quoting Table Names????

From
Mike Mascari
Date:
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@mascari.com