54

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?

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
BullyWiiPlaza
  • 14,779
  • 7
  • 95
  • 158

3 Answers3

75

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

SELECT "Continent"
FROM network.countries
...
Eugene Lisitsky
  • 11,103
  • 4
  • 33
  • 56
  • 3
    Ah yes :) The column name starts with an uppercase letter while other columns did not. Your answer is correct, it works with quotes – BullyWiiPlaza Jan 11 '17 at 12:55
  • 3
    @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 – a_horse_with_no_name Jan 11 '17 at 13:26
  • 2
    @a_horse_with_no_name: Well I didn't set up that database but I have to work with it... – BullyWiiPlaza Jan 11 '17 at 21:38
  • 1
    You need to quote "Continent" to prevent PostgreSQL from converting it into lowercase. See https://stackoverflow.com/a/55297938/9450152 – kotchwane Nov 15 '21 at 09:39
6

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' "))
ISONecroMAn
  • 1,352
  • 2
  • 15
  • 21
  • 8
    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.). – Ilja Everilä May 07 '20 at 11:00
5

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:

image for table user

404
  • 6,712
  • 2
  • 24
  • 43
Kaori
  • 51
  • 1
  • 1
  • 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 https://stackoverflow.com/questions/55297807/when-do-postgres-column-or-table-names-need-quotes-and-when-dont-they – kotchwane Nov 15 '21 at 09:38