Jaa

TABLE_OR_VIEW_NOT_FOUND error class

  • Artikkeli

SQLSTATE: 42P01

The table or view <relationName> cannot be found. Verify the spelling and correctness of the schema and catalog.

If you did not qualify the name with a schema, verify the current_schema() output, or qualify the name with the correct schema and catalog.

To tolerate the error on drop use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS.

Parameters

  • relationName: The name of the specified relation (table or view) that cannot be found.

Explanation

Persisted tables and views consists of a three name parts: <catalog>.<schema>.<relation>. If you do not specify all three parts of the name, it is implicitly completed using the current catalog or the current schema. This is similar to the way how the working directory of your filesystem influences which files you can see, unless you fully specify the path.

Temporary views or common table expressions (CTE) only exist within the session or query and must never be qualified.

The most common reason for not finding a table or view are:

  • The object simply doesn’t exist.
  • The object name, schema, or catalog was misspelled.
  • The object is not located in the current schema.
  • The user has no access to the object and therefore cannot see it.

Mitigation

The mitigation of the error depends on the cause:

  • Did you spell the table or view, schema, or catalog name incorrectly?

    Correct the spelling.

  • Did you not fully qualify the name and the result of VALUES current_schema() does not match the qualified name of the table or view?

    Explicitly qualify the relationName with its schema and catalog, or issue a USE SCHEMA command to set the desired implicit schema.

  • Did you reference a temporary view, but it was in a previous, expired, or different session?

    Recreate the temporary view using CREATE TEMPORARY VIEW <relationName> …, or switch to using a permanent view.

  • Did you reference a common table expression (CTE), but it is out of scope?

    Move the definition of the CTE to the outermost query. That is the beginning of the statement, so it is visible everywhere within the statement.

  • Do you want to issue a DDL statement, such as ‘DROP TABLE’ just in case the object exists?

    Issue the statement using the IF EXISTS clause, such as: DROP TABLE <relationName> IF EXISTS.

  • Do you know the object exists, but you cannot see it in SHOW TABLE?

    Contact your administrator to get access to the table granted. This may need to include access to the schema and catalog as well.

  • It is unclear why you cannot resolve the table or view?

    Refer to Table and view resolution for a detailed description of name resolution.

Examples

-- The table is located in othercat.someschema
> SELECT count(*) FROM t;
 [TABLE_OR_VIEW_NOT_FOUND] The table or view `t` cannot be found.

> VALUES current_schema();
 default

-- Change the current schema
> USE SCHEMA othercat.someschema;
> SELECT count(*) FROM T;
 1

-- Alternatively qualify the table
> SELECT count(*) FROM othercat.someschema.t;
 1

-- A reference to a CTE in the wrong scope:
> SELECT count(1) FROM (WITH v(c1) AS (VALUES (1)) VALUES(2)) AS t(c1), v;
 [TABLE_OR_VIEW_NOT_FOUND] The table or view `v` cannot be found.

-- Move the CTE to top level
> WITH v(c1) AS (VALUES (1))
  SELECT count(1) FROM VALUES(2) AS t(c1), v;
 1

-- Dropping a non existing view
> DROP VIEW v;
 [TABLE_OR_VIEW_NOT_FOUND] The table or view `v` cannot be found.

> DROP VIEW IF EXISTS v;