I keep getting the error "relation [TABLE] does not exist"

sql postgresql quoted-identifier
Berra2k picture Berra2k · Dec 30, 2017 · Viewed 13.4k times · Source

I have been trying to query two tables in my database. In the server explorer I can see both tables and even see the columns within them. We'll call them Schema.table1 and Schema.table2 where "Schema" has its first letter capitalized. I have tried running the following queries: 

select * from Schema.table1;

Where I get the following error:

ERROR: relation "schema.table1" does not exist

I then tried running the next query thinking maybe the capitalization in the schema made a difference.

Select * from "Schema.table1"; 
select "ID" from "Schema.table1";

But the same error persisted:

ERROR: relation "Schema.table1" does not exist

I later tried to specify the schema path with "SET search_path to "Schema1" and ran a query on the tables which again provided me the same error. Any ideas or help would be greatly appreciated.

Answer

a_horse_with_no_name picture a_horse_with_no_name · Feb 21, 2018

Each element has to be quoted individually:

select "ID" 
from "Schema"."table1";

More details about quoted identifiers are in the manual

Related questions

Cannot simply use PostgreSQL table name ("relation does not exist")

I'm trying to run the following PHP script to do a simple database query: $db_host = "localhost"; $db_name = "showfinder"; $username = "user"; $password = "password"; $dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password") or die('Could …

Read More
PostgreSQL column 'foo' does not exist

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 …

Read More
PostgreSQL "Column does not exist" but it actually does

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: …

Read More