PostgreSQL“列不存在"但实际上确实如此 [英] PostgreSQL "Column does not exist" but it actually does

问题描述

我正在编写一个 Java 应用程序来自动构建和运行 SQL 查询.对于许多表，我的代码工作正常，但在某个表上它会因抛出以下异常而卡住:

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

已运行的查询如下:

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

这实际上从列中返回 5 个非空值.

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

我不明白为什么我会在 pgAdmin 4.我可以看到有一个名为 Network 的架构，其中包含表 countries 并且该表有一个名为 Continent 的列，正如预期的那样.

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.

由于所有列、模式和表名都是由应用程序本身检索的，我认为没有拼写或语义错误，所以为什么 PostgreSQL 会导致问题吗?在 pgAdmin4 中运行查询也不使用建议的 countries.Continent 正在工作.

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.

我的 PostgreSQL 版本是目前最新的:

My PostgreSQL version is the newest as of now:

$ psql --version
psql (PostgreSQL) 9.6.1

如何成功运行查询?

推荐答案

尽量用双引号括起来——比如查询中的"Continent":

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

SELECT "Continent"
FROM network.countries
...

这篇关于PostgreSQL“列不存在"但实际上确实如此的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！