PostgreSQL列'foo'不存在 [英] PostgreSQL column 'foo' does not exist

问题描述

我有一个表，其中有20个整数列和一个名为'foo'的文本列。

I have a table that has 20 integer columns and 1 text column named 'foo'

如果我运行query：

If I run query:

SELECT * from table_name where foo is NULL

我收到错误：

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

结果输出显示foo作为列之一....
我猜我必须在查询中做一些特殊的事情，因为foo是一个文本列...

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...

感谢您的帮助（POSTGRESQL 8.3）

Thanks for the help (POSTGRESQL 8.3)

推荐答案

您不小心创建了带有尾随空格的列名称，可能是phpPGadmin创建了带有双引号的列名：

You accidentally created the column name with a trailing space and presumably phpPGadmin created the column name with double quotes around it:

create table your_table (
    "foo " -- ...
)

这将给你一个看起来像 foo 的列，但是你必须双引号，并在使用它时包括空格：

That would give you a column that looked like it was called foo everywhere but you'd have to double quote it and include the space whenever you use it:

select ... from your_table where "foo " is not null

最佳实践是使用PostgreSQL的小写无引号列名。在phpPGadmin中应该有一个设置，它会告诉它不引用标识符（例如表和列名），但唉，我不使用phpPGadmin，所以我不在那个设置（或即使存在）。

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列'foo'不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！