PostgreSQL column 'foo' does not exist

Question

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)

Lille

What do you see if you go into psql and type "\d table_name"? – Redd

How did you create the column? Was it created as "Foo" or "Foo " or similar? – Squashy

@PaulTomblin I am accessing using phpPGmyadmin, don't have access to cmd line. – Lille

@muistooshort It is "foo". I checked for trailing spaces – Lille

What SQL was used to create the table? You should be able to dig that out of phpPGmyadmin. – Squashy

@muistooshort your original guess was correct. Digging through phpPGadmin, there is a trailing space, but when it outputs the name, there is none (I am guessing, eaten by HTML parsing). Add an answer, so I may accept it:D – Lille

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

Squashy

lifesaver! this really helped me. Created through Postico and couldn't figure out for the life of me why this wasn't working. It had added '""' to the column name – Mosra

Had the same issue using Django where I had model field names with capital letters (e.g date_time_GMT and date_time_UTC). Somehow " were added to the column names in Postgresql. Solution was just to rename the model fields in Django to be all lower case (e.g date_time_utc and date_time_gmt). After a migration it worked as expected with no " added in Postgresql. – Solvolysis

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

Motoneuron

I used the ORM Sequelize for Node.js to generate my tables. It automatically gives columns "createdAt" and "updatedAt" which can't be selected on the command line without wrapping the names in quotes. – Contentment

Answer 3

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:

SELECT * FROM table_name where "Foo" IS NULL

Adrianople

This is the answer that fixed it for me. Kind of a weird Postgres quirk to covert column names to lower case but require the exact match with case. Thanks! – Flume

Answer 4

I fixed it by changing the quotation mark (") with apostrophe (') inside Values. For instance:

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');

Assuming datetime column is VarChar.

Serene

This really motivated my team to adopt postgres as our database of choice... – Outage

Answer 5

In my case when i run select query it works and gives desired data. But when i run query like

select * from users where email = "[email protected]"

It shows this error

ERROR:  column "[email protected]" does not exist
LINE 2: select * from users where email = "[email protected]...
                                          ^
SQL state: 42703
Character: 106

Then i use single quotes instead of double quotes for match condition, it works. for ex.

select * from users where email = '[email protected]'

Mote

Answer 6

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.

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 not in 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)

Implication

Answer 7

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.

The column looked like it was called anID but 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

Some more detail:

Doing postgres=# SELECT * FROM test; gave:

  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:

ERROR:  column "anid" does not exist 
LINE 1: SELECT anID FROM test;
                ^

Just looking at the column names didn't help: 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:

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:

 anID 
------
    1
    2
    3
(3 rows)

Same moral, don't use quotes.

Adagietto

Answer 8

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(' ',' ',' ');

Workaday

Answer 9

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.

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.

You can alter the column in phppgadmin and change the column name to all lower case this issue will go away.

Puff

According to your answer, you think that the problem might be that the column name is not "foo". You should then clearly say it and may be ask for the result of the select statement from where you could see, how it was created. Just to be sure that this is the case. It it a good practise to provide a solution to this case as proposed by your answer. So please edit your answer to provide a more helpful solution. – Crowley

Answer 10

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.

Netta

Answer 11

I also ran into this error when I was using Dapper and forgot to input a parameterized value.

To fix I had to ensure that the object passed in as a parameter had properties matching the parameterised values in the SQL string.

Herndon

Answer 12

I am working with address data and used pandas to load the data to Postgres. The pandas_usaddress library did a great job parsing my address, but put the new columns InSpeCific case. crazy. But now I have to use double quotes with each column name. select "ZipCode" from har_addresses; And I get my data.

Deplume