Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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)

like image 505
nulltorpedo Avatar asked Apr 17 '12 23:04

nulltorpedo


People also ask

Does not exist in PostgreSQL?

The NOT EXISTS is opposite to EXISTS . It means that if the subquery returns no row, the NOT EXISTS returns true. If the subquery returns one or more rows, the NOT EXISTS returns false.

Does not exist at character Postgres?

In PostgreSQL, a relation does not exist error happens when you reference a table name that can't be found in the database you currently connect to. In the case above, the error happens because Sequelize is trying to find Users table with an s , while the existing table is named User without an s .

How do I rename a column in PostgreSQL?

The syntax to rename a column in a table in PostgreSQL (using the ALTER TABLE statement) is: ALTER TABLE table_name RENAME COLUMN old_name TO new_name; table_name.

Where is PostgreSQL?

The PostgreSQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables. If the given condition is satisfied, only then it returns specific value from the table.


2 Answers

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

like image 200
mu is too short Avatar answered Sep 29 '22 11:09

mu is too short


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.

like image 38
9000 Avatar answered Sep 29 '22 13:09

9000