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?
Try to take it into double quotes – like
"Continent" in the query:
SELECT "Continent" FROM network.countries ...
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" "))
This problem occurs in postgres because the table name is not tablename instead it is “tablename”.
If it shows user as table name,
than table name is “user”.