Thread: 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?
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
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?
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
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
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
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