This page documents name resolution in CockroachDB.
To reference an object (e.g., a table) in a query, you can specify a database, a schema, both, or neither. To resolve which object a query references, CockroachDB scans the appropriate namespaces, following a set of rules outlined below.
For compatibility with PostgreSQL, CockroachDB supports a three-level structure for names. This is called the "naming hierarchy".
In the naming hierarchy, the path to a stored object has three components:
- database name
- schema name
- object name
When you first start a cluster, a number of preloaded databases and schemas are included, including the
defaultdb database and the
public schema. By default, objects (e.g., tables) are stored in the preloaded
public schema, in the current database (
defaultdb, by default).
In addition to the
public schema, CockroachDB supports a fixed set of system catalog schemas, available in every database, that provide ancillary, non-stored data to client applications. For example,
information_schema is provided for compatibility with the SQL standard, and
pg_extension are provided for compatibility with PostgreSQL.
To create a new database, use a
CREATE DATABASE statement. To create a new schema, use a
CREATE SCHEMA statement. The list of all databases can be obtained with
SHOW DATABASES. The list of all schemas for a given database can be obtained with
SHOW SCHEMAS. The list of all objects for a given schema can be obtained with other
Migrating namespaces from previous versions of CockroachDB
In CockroachDB versions < v20.2, user-defined schemas are not supported, and all objects created in a given database use the
public schema. To provide a multi-level structure for stored objects in earlier versions of CockroachDB, we have recommended using database namespaces instead of schema namespaces.
In CockroachDB versions >= v20.2, we recommend using schema namespaces, not database namespaces, to create a naming structure that is more similar to PostgreSQL.
If you are upgrading to v20.2, take any combination of the following actions after the upgrade is complete:
ALTER DATABASE ... CONVERT TO SCHEMAstatement to convert databases into schemas. This statement is particularly useful if you created databases for the sole purpose of creating a multi-level naming structure. When you convert a database to a schema, all tables, sequences, and user-defined types in the database become child objects of a new schema, and the database is deleted. Note that you cannot convert databases that contain user-defined schemas or views.
Create new schemas in databases on your cluster. After the schemas are created, use
ALTER TABLE ... RENAME,
ALTER SEQUENCE ... RENAME,
ALTER TYPE ... RENAME, or
ALTER VIEW ... RENAMEstatements to move objects between databases as needed. To move objects between schemas, use
ALTER TABLE ... SET SCHEMA,
ALTER SEQUENCE ... SET SCHEMA, or
ALTER VIEW ... SET SCHEMA.
If your cluster contains cross-database references (e.g., a cross-database foreign key reference, or a cross-database view reference), use the relevant
ALTER TYPE, or
ALTER VIEWstatements to move any cross-referencing objects to the same database, but different schemas. Cross-database object references were allowed in earlier versions of CockroachDB to make database-object naming hierarchies more flexible for users. In v20.2, creating cross-database references are disabled for foreign keys, views, and sequence ownership. For details, see tracking issue.
How name resolution works
Name resolution occurs separately to look up existing objects and to decide the full name of a new object.
The rules to look up an existing object are as follows:
- If the name already fully specifies the database and schema, use that information.
- If the name has a single-component prefix (e.g., a schema name), try to find a schema with the prefix name in the current database and current schema. If that fails, try to find the object in the
publicschema of a database with the prefix name.
- If the name has no prefix, use the search path with the current database.
Similarly, the rules to decide the full name of a new object are as follows:
- If the name already fully specifies the database and schema, use that.
- If the name has a single-component prefix (e.g., a schema name), try to find a schema with that name. If no such schema exists, use the
publicschema in the database with the prefix name.
- If the name has no prefix, use the current schema in the current database.
Parameters for name resolution
The current database is used when a name is unqualified or has only one component prefix. It is the current value of the
database session variable.
You can inspect the list of valid database names that can be specified in
For client apps that connect to CockroachDB using a URL of the form
postgres://..., the initial value of the
databasesession variable can be set using the path component of the URL. For example,
mydbwhen the connection is established.
The search path is used when a name is unqualified (i.e., has no prefix). It lists the schemas where objects are looked up. Its first element is also the current schema where new objects are created.
- You can set the current search path with
SET search_pathand inspected it with
- You can inspect the list of valid schemas that can be listed in
- By default, the search path contains
pg_extension. For compatibility with PostgreSQL,
pg_catalogis forced to be present in
search_pathat all times, even when not specified with
- To mimic the behavior in PostgreSQL, CockroachDB will attempt a resolution to
pg_extensionprior to attempting
The current schema is used as target schema when creating a new object if the name is unqualified (has no prefix).
The current schema is always the first value of
search_path, for compatibility with PostgreSQL.
You can inspect the current schema using the special built-in function/identifier
Index name resolution
- Index names are resolved relative to a table name using the
DROP INDEX tbl@idx;. This is the default and most common syntax.
- Index names are resolved by searching all tables in the current schema to find a table with an index named
DROP INDEX idx;or (with optional schema prefix)
DROP INDEX public.idx;. This syntax is necessary for Postgres compatibility because Postgres index names live in the schema namespace such that e.g.,
public.idxwill resolve to the index
idxof some table in the public schema. This capability is used by some ORMs.
The name resolution algorithm for index names supports both partial and complete qualification, using the same name resolution rules as other objects.
The examples below use the following logical schema as a starting point:
> CREATE DATABASE mydb;
> CREATE TABLE mydb.mytable(x INT);
> SET database = mydb;
Lookup with unqualified names
An unqualified name is a name with no prefix, that is, a simple identifier.
> SELECT * FROM mytable;
This uses the search path over the current database. The search path is
$user by default, in the current database. If a
$user schema does not exist, the search path resolves to the
public schema. In this case, there is no
$user schema, and the resolved name is
> SET database = system;
> SELECT * FROM mytable;
pq: relation "mytable" does not exist
This uses the search path over the current database, which is now
system. No schema in the search path contain table
mytable, so the
look up fails with an error.
Lookup with fully qualified names
A fully qualified name is a name with two prefix components, that is, three identifiers separated by periods.
> SELECT * FROM mydb.public.mytable;
Both the database and schema components are specified. The lookup succeeds if and only if the object exists at that specific location.
Lookup with partially qualified names
A partially qualified name is a name with one prefix component, that is, two identifiers separated by a period. When a name is partially qualified, CockroachDB will try to use the prefix as a schema name first; and if that fails, use it as a database name.
> SELECT * FROM public.mytable;
This looks up
mytable in the
public schema of the current
database. If the current database is
mydb, the lookup succeeds.
To ease development in multi-database scenarios, CockroachDB also allows queries to specify a database name in a partially qualified name. For example:
> SELECT * FROM mydb.mytable;
In that case, CockroachDB will first attempt to find a schema called
mydb in the current database. When no such schema exists (which is
the case with the starting point in this section), it then tries to
find a database called
mydb and uses the
public schema in that. In
this example, this rule applies and the fully resolved name is
Using the search path to use tables across schemas
Suppose that a client frequently accesses a stored table as well as a virtual table in the Information Schema. Because
information_schema is not in the search path by default, all queries that need to access it must mention it explicitly.
> SELECT * FROM mydb.information_schema.schemata; -- valid
> SELECT * FROM information_schema.schemata; -- valid; uses mydb implicitly
> SELECT * FROM schemata; -- invalid; information_schema not in search_path
For clients that use
information_schema often, you can add it to the
search path to simplify queries. For example:
> SET search_path = public, information_schema;
> SELECT * FROM schemata; -- now valid, uses search_path
Databases with special names
When resolving a partially qualified name with just one component
prefix, CockroachDB will look up a schema with the given prefix name
first, and only look up a database with that name if the schema lookup
fails. This matters in the (likely uncommon) case where you wish your
database to be called
> CREATE DATABASE public;
> SET database = mydb;
> CREATE TABLE public.mypublictable (x INT);
CREATE TABLE statement in this example uses a partially
qualified name. Because the
public prefix designates a valid schema
in the current database, the full name of
mydb.public.mypublictable. The table is created in database
To create the table in database
public, one would instead use a
fully qualified name, as follows:
> CREATE DATABASE public;
> CREATE TABLE public.public.mypublictable (x INT);
New clusters and existing clusters upgraded to v21.1 or later will include auto-generated databases, with the following purposes:
- The empty
defaultdbdatabase is used if a client does not specify a database in the connection parameters.
movrdatabase contains data about users, vehicles, and rides for the vehicle-sharing app MovR.
- The empty
postgresdatabase is provided for compatibility with PostgreSQL client applications that require it.
startrekdatabase contains quotes from episodes.
systemdatabase contains CockroachDB metadata and is read-only.
All databases except for the
system database can be deleted if they are not needed.
Do not query the
system database directly. Instead, use objects within the system catalogs.