SQL PostgreSQL 列 'foo' 不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10200769/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL column 'foo' does not exist
提问by nulltorpedo
I have a table that has 20 integer columns and 1 text column named 'foo'
我有一个表，它有 20 个整数列和 1 个名为“foo”的文本列
If I run query:
如果我运行查询：
SELECT * from table_name where foo is NULL
I get error:
我得到错误：
ERROR: column "foo" does not exist
I have checked myself that his column indeed exists. If I do something like:
我自己检查过他的专栏确实存在。如果我做这样的事情：
SELECT * from table_name where count is NULL
The resulting output shows 'foo' as one of the columns.... I am guessing I have to do something special in the query because foo is a text column...
结果输出显示 'foo' 作为列之一......我猜我必须在查询中做一些特殊的事情，因为 foo 是一个文本列......
Thanks for the help (POSTGRESQL 8.3)
感谢您的帮助（POSTGRESQL 8.3）
采纳答案by mu is too short
You accidentally created the column name with a trailing space and presumably phpPGadmin created the column name with double quotes around it:
您不小心创建了带有尾随空格的列名，并且大概是 phpPGadmin 创建的列名周围带有双引号：
create table your_table (
"foo " -- ...
)
That would give you a column that looked like it was called fooeverywhere but you'd have to double quote it and include the space whenever you use it:
这会给你一个看起来像在foo任何地方都被调用的列，但你必须双引号它并在你使用它时包括空格：
select ... from your_table where "foo " is not null
The best practice is to use lower case unquoted column names with PostgreSQL. There should be a setting in phpPGadmin somewhere that will tell it to not quote identifiers (such as table and column names) but alas, I don't use phpPGadmin so I don't where that setting is (or even if it exists).
最佳实践是在 PostgreSQL 中使用小写的不带引号的列名。phpPGadmin 中的某处应该有一个设置，它会告诉它不要引用标识符（例如表名和列名），但是唉，我不使用 phpPGadmin，所以我不知道该设置在哪里（或者即使它存在）。
回答by 9000
If for some reason you have created a mixed-case or upper-case column name, you need to quote it, or get this error:
如果由于某种原因你创建了一个大小写混合或大写的列名，你需要引用它，或者得到这个错误：
test=> create table moo("FOO" int);
CREATE TABLE
test=> select * from moo;
FOO
-----
(0 rows)
test=> select "foo" from moo;
ERROR: column "foo" does not exist
LINE 1: select "foo" from moo;
^
test=> _
Note how the error message gives the case in quotes.
请注意错误消息如何在引号中给出案例。
回答by Russell Fox
PostreSQL apparently converts column names to lower case in a sql query - I've seen issues where mixed case column names will give that error. You can fix it by putting the column name in quotation marks:
PostreSQL 显然在 sql 查询中将列名转换为小写 - 我已经看到混合大小写列名会产生该错误的问题。您可以通过将列名放在引号中来修复它：
SELECT * FROM table_name where "Foo" IS NULL
回答by kgrittn
As others suggested in comments, this is probably a matter of upper-case versus lower-case, or some whitespace in the column name. (I'm using an answer so I can format some code samples.) To see what the column names really are, try running this query:
正如其他人在评论中建议的那样，这可能是大写与小写的问题，或者列名中的一些空格。（我正在使用答案，以便我可以格式化一些代码示例。）要查看列名的真正含义，请尝试运行以下查询：
SELECT '"' || attname || '"', char_length(attname)
FROM pg_attribute
WHERE attrelid = 'table_name'::regclass AND attnum > 0
ORDER BY attnum;
You should probably also check your PostgreSQL server log if you can, to see what it reports for the statement.
如果可以，您可能还应该检查您的 PostgreSQL 服务器日志，以查看它为语句报告的内容。
If you quote an identifier, everything in quotes is part of the identifier, including upper-case characters, line endings, spaces, and special characters. The only exception is that two adjacent quote characters are taken as an escape sequence for one quote character. When an identifier is notin quotes, all letters are folded to lower-case. Here's an example of normal behavior:
如果引用标识符，引号中的所有内容都是标识符的一部分，包括大写字符、行尾、空格和特殊字符。唯一的例外是两个相邻的引号字符被视为一个引号字符的转义序列。当标识符不在引号中时，所有字母都折叠为小写。下面是一个正常行为的例子：
test=# create table t (alpha text, Bravo text, "Charlie" text, "delta " text);
CREATE TABLE
test=# select * from t where Alpha is null;
alpha | bravo | Charlie | delta
-------+-------+---------+--------
(0 rows)
test=# select * from t where bravo is null;
alpha | bravo | Charlie | delta
-------+-------+---------+--------
(0 rows)
test=# select * from t where Charlie is null;
ERROR: column "charlie" does not exist
LINE 1: select * from t where Charlie is null;
^
test=# select * from t where delta is null;
ERROR: column "delta" does not exist
LINE 1: select * from t where delta is null;
^
The query I showed at the top yields this:
我在顶部显示的查询产生了这个：
?column? | char_length
-----------+-------------
"alpha" | 5
"bravo" | 5
"Charlie" | 7
"delta " | 6
(4 rows)
回答by BradP
It could be quotes themselves that are the entire problem. I had a similar problem and it was due to quotes around the column name in the CREATE TABLE statement. Note there were no whitespace issues, just quotes causing problems.
可能是引用本身就是整个问题。我遇到了类似的问题，这是由于 CREATE TABLE 语句中列名周围的引号引起的。请注意，没有空格问题，只是引起问题的引号。
The column looked like it was called anIDbut was really called "anID". The quotes don't appear in typical queries so it was hard to detect (for this postgres rookie). This is on postgres 9.4.1
该列看起来像被调用，anID但实际上被调用了"anID"。引号不会出现在典型的查询中，因此很难检测到（对于这个 postgres 菜鸟）。这是在 postgres 9.4.1 上
Some more detail:
更多细节：
Doing postgres=# SELECT * FROM test;gave:
做postgres=# SELECT * FROM test;给：
anID | value
------+-------
1 | hello
2 | baz
3 | foo (3 rows)
but trying to select just the first column SELECT anID FROM test;resulted in an error:
但是尝试仅选择第一列SELECT anID FROM test;导致错误：
ERROR: column "anid" does not exist
LINE 1: SELECT anID FROM test;
^
Just looking at the column names didn't help:
postgres=# \d test;
只看列名没有帮助：
postgres=# \d test;
Table "public.test"
Column | Type | Modifiers
--------+-------------------+-----------
anID | integer | not null
value | character varying |
Indexes:
"PK on ID" PRIMARY KEY, btree ("anID")
but in pgAdmin if you click on the column name and look in the SQL pane it populated with:
但是在 pgAdmin 中，如果您单击列名并查看它填充的 SQL 窗格：
ALTER TABLE test ADD COLUMN "anID" integer;
ALTER TABLE test ALTER COLUMN "anID" SET NOT NULL;
and lo and behold there are the quoutes around the column name. So then ultimately postgres=# select "anID" FROM test;works fine:
瞧，列名周围有引号。那么最终postgres=# select "anID" FROM test;工作正常：
anID
------
1
2
3
(3 rows)
Same moral, don't use quotes.
同样的道德，不要使用引号。
回答by Menelaos Kotsollaris
I fixed it by changing the quotation mark(") with apostrophe(') inside Values. For instance:
我通过在 Values 中使用撇号( ')更改引号( ") 来修复它。例如：
insert into trucks ("id","datetime") VALUES (862,"10-09-2002 09:15:59");
insert into trucks ("id","datetime") VALUES (862,"10-09-2002 09:15:59");
Becomes this:
变成这样：
insert into trucks ("id","datetime") VALUES (862,'10-09-2002 09:15:59');
insert into trucks ("id","datetime") VALUES (862,'10-09-2002 09:15:59');
Assuming datetimecolumn is VarChar.
假设datetime列是VarChar。
回答by meenal
the problem occurs because of the name of column is in camel case internally it wraps it in " "(double quotes) to solve this, at the time of inserting values in table use single quotes ('')
出现问题是因为列的名称在内部是驼峰式的，它用“”（双引号）包裹它来解决这个问题，在表中插入值时使用单引号（''）
e.g. insert into schema_name.table_name values(' ',' ',' ');
例如 插入 schema_name.table_name values(' ',' ',' ');
回答by Pavan Aripirala Venkata
We ran into this issue when we created the table using phppgadmin client. With phppgadmin we did not specify any double quotes in column name and still we ran into same issue.
我们在使用 phppgadmin 客户端创建表时遇到了这个问题。使用 phppgadmin 我们没有在列名中指定任何双引号，但我们仍然遇到了同样的问题。
It we create column with caMel case then phpPGAdmin implicitly adds double quotes around the column name. If you create column with all lower case then you will not run into this issue.
如果我们使用 caMel 大小写创建列，那么 phpPGAdmin 会在列名周围隐式添加双引号。如果您创建全小写的列，则不会遇到此问题。
You can alter the column in phppgadmin and change the column name to all lower case this issue will go away.
您可以在 phppgadmin 中更改列并将列名更改为全部小写，此问题将消失。
回答by Titulus
I fixed similar issues by qutating column name
我通过引用列名修复了类似的问题
SELECT * from table_name where "foo" is NULL;
In my case it was just
就我而言，这只是
SELECT id, "foo" from table_name;
without quotes i'v got same error.
没有引号，我有同样的错误。
