SQL Error: relation "schema.table" does not exist" (Community)

View the original community article here

Last tested: Feb 2021

This error message can appear when a table no longer exists but still shows in the list of tables for this schema.

Error Message

• Error Running SQL ERROR: relation "schema.table" does not exist

Troubleshooting

Go to the gear option next to the connection name and hit "Refresh Tables and Schema" if the table has been renamed/deleted then it will no longer show up under the reference name that is causing the error.

This can also happen if the table is defined with mixed-case spelling and you try to query it without double-quotes. See this stackoverflow post

In other words, if hotelList was created with mixed-case spelling, this won't work:

play_arrow

SELECT *
FROM hotellist
LIMIT 10

Because the tablename is converted to lowercase when the query is run, this also won't work:

play_arrow

SELECT *
FROM hotelList
LIMIT 10

To avoid this issue, use double-quotes to use the specific mixed-case spelling as the table is defined. This will work:

play_arrow

SELECT *
FROM "hotelList"
LIMIT 10

It is also important to check for fields that reference a field from a joined view WITHOUT ${} syntax, in this case the error surfaces because Looker does not know where to look for the referenced field.

For Snowflake, specifically, ensure that the table names are all UPPERCASE in the database. As per this doc, unquoted values are defaulted to uppercase in Snowflake. That's why we'll usually see the error:
SQL compilation error: Object 'DATABASE.SCHEMA.TABLE_NAME' does not exist when we run
SELECT Procfile README.md all_cards.json body_after.txt body_before.txt body_during.txt ids.txt jq main.sh tmp FROM schema.table_name

If we run:
SELECT Procfile README.md all_cards.json body_after.txt body_before.txt body_during.txt ids.txt jq main.sh tmp FROM "schema"."table_name"
we should see the query run successfully because the lower case will be preserved.