Обсуждение: Help with queries...
Why do I need to add "" to a table when doing a query? I've checked the examples and I found no one has " around the table names. It is something with the configuration?
If I do:
select * from users;
ERROR: relation "users" does not exist
but:
select * from "Users";
returns all the data I want...
Right now I'm using postgresql 8.0.1
On Thu, Feb 24, 2005 at 02:33:28PM -0600, Cristian Prieto wrote: > select * from users; > ERROR: relation "users" does not exist > > but: > select * from "Users"; > returns all the data I want... See "Identifiers and Keywords" in the "SQL Syntax" chapter of the documentation, especially the parts that talk about case and quoting: http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Cristian Prieto wrote: > Why do I need to add "" to a table when doing a query? I've checked > the examples and I found no one has " around the table names. It is > something with the configuration? > If I do: > > select * from users; > ERROR: relation "users" does not exist > > but: > select * from "Users"; > returns all the data I want... > > Right now I'm using postgresql 8.0.1 Because PostgreSQL folds all non quoted relation names to lowercase. It is best just to have your relations be lowercase and then you don't have to quote. Sincerely, Joshua D. Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Вложения
The book says:
There is a second kind of identifier: the delimited identifier or quoted
identifier. It is formed by enclosing an arbitrary sequence of characters in
double-quotes ("). A delimited identifier is always an identifier, never a
key word. So "select" could be used to refer to a column or table named
"select", whereas an unquoted select would be taken as a key word and would
therefore provoke a parse error when used where a table or column name is
expected. The example can be written with quoted identifiers like this:
UPDATE "my_table" SET "a" = 5;
Quoted identifiers can contain any character other than a double quote
itself. (To include a double quote, write two double quotes.) This allows
constructing table or column names that would otherwise not be possible,
such as ones containing spaces or ampersands. The length limitation still
applies.
But my table name Grupos is not a reserved keyword, so SELECT * FROM Grupos;
should work, right??? but guess what... It doesn't work too... I need to
pass SELECT * FROM "Grupos";
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Cristian Prieto" <cristian@clickdiario.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, February 24, 2005 2:44 PM
Subject: Re: [GENERAL] Help with queries...
> On Thu, Feb 24, 2005 at 02:33:28PM -0600, Cristian Prieto wrote:
>
>> select * from users;
>> ERROR: relation "users" does not exist
>>
>> but:
>> select * from "Users";
>> returns all the data I want...
>
> See "Identifiers and Keywords" in the "SQL Syntax" chapter of the
> documentation, especially the parts that talk about case and quoting:
>
> http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> > But my table name Grupos is not a reserved keyword, so SELECT * FROM > Grupos; should work, right??? but guess what... It doesn't work too... > I need to pass SELECT * FROM "Grupos"; That is correct. Because you have a capital letter in the table name. Sincerely, Joshua D. Drake > > > > ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> > To: "Cristian Prieto" <cristian@clickdiario.com> > Cc: <pgsql-general@postgresql.org> > Sent: Thursday, February 24, 2005 2:44 PM > Subject: Re: [GENERAL] Help with queries... > > >> On Thu, Feb 24, 2005 at 02:33:28PM -0600, Cristian Prieto wrote: >> >>> select * from users; >>> ERROR: relation "users" does not exist >>> >>> but: >>> select * from "Users"; >>> returns all the data I want... >> >> >> See "Identifiers and Keywords" in the "SQL Syntax" chapter of the >> documentation, especially the parts that talk about case and quoting: >> >> http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS >> >> >> -- >> Michael Fuhr >> http://www.fuhr.org/~mfuhr/ >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Вложения
"Cristian Prieto" <cristian@clickdiario.com> writes:
> The book says:
> [snip]
> But my table name Grupos is not a reserved keyword, so SELECT * FROM Grupos;
> should work, right??? but guess what... It doesn't work too... I need to
> pass SELECT * FROM "Grupos";
You didn't read far enough:
Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to lower case. For example, the identifiers FOO,
foo, and "foo" are considered the same by PostgreSQL, but "Foo" and
"FOO" are different from these three and each other.
Had you originally created the table as Grupos without quotes, then you
could refer to it as Grupos without quotes. But you evidently created
it with quotes (or used a tool that added quotes for you) and now you're
stuck with using quotes forevermore, unless you rename the table.
regards, tom lane