Postgres query fails without schema

Question

THis doesn't work, resulting in ERROR: relation "user" does not exist

select * from "user"

This one does

select * from "dbo"."user"

Answer 1

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 on search_path)
• pg_temp tablespaces (implicit, not listed on search_path)
• A schema named after the current user, listed as $user in search_path
• The public schema

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.