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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  create table moo  FOO  int   CREATE TABLE test  gt  select   from moo   FOO  -----  0 rows  test  gt  select  foo  from moo  ERROR   column  foo  does not exist LINE 1  select  foo  from moo                   test  gt      Note how the error message gives the case in quotes

User · Answer

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    quot user gmail com quot   It shows this error ERROR   column  quot user gmail com quot  does not exist LINE 2  select   from users where email    quot user gmail com                                                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    user gmail com

User · Answer

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

User · Answer

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

User · Answer

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

[sql] PostgreSQL column 'foo' does not exist

Examples related to sql

Examples related to postgresql

Examples related to quoted-identifier