PostgreSQL column 'foo' does not exist

sql postgresql quoted-identifier
nulltorpedo picture nulltorpedo · Apr 18, 2012 · Viewed 143.7k times · Source

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)

Answer

mu is too short picture mu is too short · Apr 18, 2012

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

Related questions

Cannot simply use PostgreSQL table name ("relation does not exist")

I'm trying to run the following PHP script to do a simple database query: $db_host = "localhost"; $db_name = "showfinder"; $username = "user"; $password = "password"; $dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password") or die('Could …

Read More
PostgreSQL "Column does not exist" but it actually does

I'm writing a Java application to automatically build and run SQL queries. For many tables my code works fine but on a certain table it gets stuck by throwing the following exception: Exception in thread "main" org.postgresql.util.PSQLException: …

Read More
sql statement error: "column .. does not exist"

Im trying from postgres console this command: select sim.id as idsim, num.id as idnum from main_sim sim left join main_number num on (FK_Numbers_id=num.id); and I've got this response: ERROR: column "fk_numbers_…

Read More