A SQL client can have access to multiple databases side-by-side. The
same table name (e.g.,
orders) can exist in multiple
databases. When a query specifies a table name without a database
SELECT * FROM orders), how does CockroachDB know
orders table is being considered?
This page details how CockroachDB performs name resolution to answer this question.
- If the name is qualified (i.e., the name already tells where to look), use this information.
SELECT * FROM db1.orderswill look up “
orders” only in
- If the name is unqualified:
In addition to the default database configurable via
SET DATABASE, unqualified names are also looked up in the current session’s search path.
The search path is a session variable containing a list of databases, or namespaces, where names are looked up.
The current search path can set using
SET SEARCH_PATH and can be inspected using
SHOW SEARCH_PATH or
By default, the search path for new columns includes just
pg_catalog, so that queries can use PostgreSQL compatibility
functions and virtual tables in that namespace without the need to
prefix them with “
pg_catalog.” every time.