Postgresql search_path issue

user_14158694 0
Viewed 237

Based on past experience and documentation related to search_path: "The first matching table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported, even if matching table names exist in other schemas in the database."

But when I run this:

financialdw=> show search_path;
                     search_path
------------------------------------------------------
 "$user, prismdms, prism_nonrestrict, prism_restrict"
(1 row)

financialdw=> select count(*) from addr;
ERROR:  relation "addr" does not exist
LINE 1: select count(*) from addr;
                             ^
financialdw=> select count(*) from prismdms.addr;
 count
-------
 24428
(1 row)

prismdms is in my search_path and I have the necessary permissions on all tables within it. Shouldn't I be able, as the document states, query the table without the schema name qualifying it?

search-path postgresql
1 Answers

Your search_path has a single schema in it, and that single schema has the long and unlikely name of $user, prismdms, prism_nonrestrict, prism_restrict.

The double quotes should just be around $user, not the entire thing.

user_17212390
Related