THis doesn't work, resulting in ERROR: relation "user" does not exist
select * from "user"
This one does
select * from "dbo"."user"
1 Answer
Tables that aren't schema-qualified are searched for on the search_path. This doesn't search every schema. By default it only searches:
pg_catalog(implicitly always first, not listed onsearch_path)pg_temptablespaces (implicit, not listed onsearch_path)- A schema named after the current user, listed as
$userinsearch_path - The
publicschema
If you want PostgreSQL to look elsewhere you have to modify the search_path to tell it so, or fully schema-qualify the name.
See the PostgreSQL manual on search_path.
Also, note that unlike some databases the name dbo has no particular significance in PostgreSQL. It's just another name.
3 Comments
Rafs
I notice that if a view uses a table whose schema is not on the search path (say, removed after creating the view), Postgres actually returns data. When you try to run the select itself outside that view, the query fails. That happens after removing a schema from the search path.. Re-adding the schema to the view gets lost after creating the view. Annoying.
Craig Ringer
@RTD That's because the view's
search_path is resolved at view creation time and "baked in" to the view. It's good practice to explicitly qualify your references in views and functions, rather than relying on the search path.Rafs
I remember I explicitly hardcoded the schema name in the view when creating it. After saving, Postgres modified the view in its own way, which removed the schema name from the view definition since it was in the
search_path. However, after removing the schema from the search path, it looked like the view wasn't refactored... I unfortunately don't have time to test this hypothesis, thank you for the reply.
search_pathdoes not include thedboschema. What's the output ofSHOW search_path;?""$user",public"dbo?postgressearch_path. So you have to schema-qualify it. That's the point of having schemas.