PostgreSQL column 'foo' does not exist

I have a table that has 20 integer columns and 1 text column named '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...

Thanks for the help (POSTGRESQL 8.3)

Solution 1:

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

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

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

Solution 2:

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.