PostgreSQL "Column does not exist" but it actually does

Question

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: ERROR: column "continent" does not exist
  Hint: Perhaps you meant to reference the column "countries.Continent".
  Position: 8

The query that has been run is the following:

SELECT Continent
FROM network.countries
WHERE Continent IS NOT NULL
AND Continent <> ''
LIMIT 5

This essentially returns 5 non-empty values from the column.

I don't understand why I'm getting the "column does not exist" error when it clearly does in pgAdmin 4. I can see that there is a schema with the name Network which contains the table countries and that table has a column called Continent just as expected.

Since all column, schema and table names are retrieved by the application itself I don't think there has been a spelling or semantical error so why does PostgreSQL cause problems regardless? Running the query in pgAdmin4 nor using the suggested countries.Continent is working.

My PostgreSQL version is the newest as of now:

$ psql --version
psql (PostgreSQL) 9.6.1

How can I successfully run the query?

Ebbarta

PostgreSQL is case-sensitive – Whooper

@wingedpanther: It's only case-sensitive if you use those dreaded quoted identifiers. – Pyemia

Unrelated, but: psql --version gives you the version of psql, not the version of the server – Pyemia

Possible duplicate of PostgreSQL column 'foo' does not exist – Caraway

Answer 1

Try to take it into double quotes - like "Continent" in the query:

SELECT "Continent"
FROM network.countries
...

Misguided

Ah yes :) The column name starts with an uppercase letter while other columns did not. Your answer is correct, it works with quotes – Ebbarta

@BullyWiiPlaza: you should re-think how you create your tables (you apparently quoted the name when creating the table). In general it's better to never use double quotes - you will have a lot less trouble in the long run – Pyemia

@a_horse_with_no_name: Well I didn't set up that database but I have to work with it... – Ebbarta

You need to quote "Continent" to prevent PostgreSQL from converting it into lowercase. See https://mcmap.net/q/205844/-when-do-postgres-column-or-table-names-need-quotes-and-when-don-39-t-they – Dirty

Answer 2

This problem occurs in postgres because the table name is not tablename instead it is "tablename". for eg. If it shows user as table name, than table name is "user".

See this:

Bathhouse

Actually, I don’t think that is the case here. The problem occurs because OP used 'Continent' as a column name, and PostgreSQL converts all names into lowercase if they are not quoted. See #55298307 – Dirty

Answer 3

In working with SQLAlchemy environment, i have got this error with the SQL like this,

   db.session.execute(
    text('SELECT name,type,ST_Area(geom) FROM buildings WHERE type == "plaza" '))

ERROR: column "plaza" does not exist

Well, i changed == by = , Error still persists, then i interchanged the quotes, like follows. It worked. Weird!

.... 
text("SELECT name,type,ST_Area(geom) FROM buildings WHERE type = 'plaza' "))

Admonitory

It's not weird, it's how SQL works. Single quotes are for literal values, double quotes for quoted identifiers (column names, table names, etc.). – Studio

@IljaEverilä's comment helped me out. I was getting the same error but had no idea that double quoting the value in WHERE made Postgres think I was mentioning an identifier, while I actually wanted a literal value. – Sow

Answer 4

Such an error can appear when you add a space in the name of a column by mistake (for example "users ").

Stalinabad

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Cid

Answer 5

I got the same error when I do PIVOT in RedShift. My code is similar to

SELECT *
INTO output_table
FROM (
SELECT name, year_month, sales
FROM input_table
) 
PIVOT
(
SUM(sales)
FOR year_month IN ('nov_2020', 'dec_2020', 'jan_2021', 'feb_2021', 'mar_2021', 'apr_2021', 'may_2021', 'jun_2021', 'jul_2021', 'aug_2021',
                  'sep_2021', 'oct_2021', 'nov_2021', 'dec_2021', 'jan_2022', 'feb_2022', 'mar_2022', 'apr_2022', 'may_2022', 'jun_2022',
                  'jul_2022', 'aug_2022', 'sep_2022', 'oct_2022', 'nov_2022')
)

I tried year_month without any quote (got the error), year_month with double quote (got the error), and finally year_month with single quote (it works this time). This may help if someone in the same situation like my example.

Expostulate

Answer 6

QUICK FIX (TRICK)

If you have recently added a field which you have already deleted before and now trying to add the same field back then let me share you this simple trick! i did this and the problem was gone!!

so, now just delete the migration folder entirely on the app,then instead of adding that field you need to now add a field but with the name of which you have never declared on this app before, example if you are trying to add title field then create it by the name of heading and now do the migration process separately on the app and runserver, now go to admin page and look for that model and delete all the objects and come to models back and rename the field that you recently made and name it to which you were wishing it with earlier and do the migrations again and now your problem must have been gone!!

this occurs when the objects are there in the db but you added a field which wasn't there when the earlier objs were made, so by this we can delete those objs and make fresh ones again!

Schuss

I wouldn't suggest to delete the migration folder on the app, as this could cause other issues... – Kirovabad

could you please tell me some other way then to fix this problem mate? – Schuss