Error: Relation "TableName" does not exist

Hi,
When i execute the command "SELECT * from SchemaName.TableName" it works fine but when i do "SELECT * from TableName" without SchemaName

It givies the Error: "Relation 'TableName' does not exist"

What might be the issue and possible solution?

Thanks

Comments

  • This has to do with the user's search_path. This is similar to PATH at the OS level, but it's schemas vs. directories. By default a user's search_path only includes public and internal schemas where the system views are. You can alter the user's search_path to include other schemas for the current session or persistently. See the Setting Search Paths pages in the Using Multiple Schemas section of the Administrators Guide for all the details. Below is a quick example.

    VMart=> select count(*) from store.store_orders_fact;

    count

    300000
    (1 row)

    VMart=> select count(*) from store_orders_fact;
    ERROR 4566: Relation "store_orders_fact" does not exist

    VMart=> show search_path;
    name | setting
    -------------+---------------------------------------------------
    search_path | "$user", public, v_catalog, v_monitor, v_internal
    (1 row)

    VMart=> set search_path to store,public;
    SET

    VMart=> show search_path;
    name | setting
    -------------+-------------------------------------------------
    search_path | store, public, v_catalog, v_monitor, v_internal
    (1 row)

    VMart=> select count(*) from store_orders_fact;

    count

    300000
    (1 row)

    I hope it helps.

  • Thanks, it worked perfectly once I set the search path.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file