> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Name Resolution

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

This page documents **name resolution** in CockroachDB.

To reference an object (e.g., a table) in a query, you can specify the <InternalLink path="keywords-and-identifiers#identifiers">identifier</InternalLink> that refers to a database, a schema, both, or neither. To resolve which object a query references, CockroachDB scans the [appropriate namespaces](#naming-hierarchy), following the rules in [How name resolution works](#how-name-resolution-works).

## Naming hierarchy

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

A CockroachDB cluster can store multiple databases. Each database can store multiple schemas, and each schema can store multiple <InternalLink path="create-table">tables</InternalLink>, <InternalLink path="views">views</InternalLink>, <InternalLink path="create-sequence">sequences</InternalLink>, and <InternalLink path="enum">user-defined types</InternalLink>.

When you first <InternalLink path="start-a-local-cluster">start a cluster</InternalLink>, a number of <InternalLink path="show-databases#preloaded-databases">preloaded databases</InternalLink> 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](#current-database) (`defaultdb`, by default).

In addition to the `public` schema, CockroachDB supports a fixed set of <InternalLink path="system-catalogs">system catalog schemas</InternalLink>, available in every database, that provide ancillary, non-stored data to client applications. For example, <InternalLink path="information-schema">`information_schema`</InternalLink> is provided for compatibility with the SQL standard, and <InternalLink path="pg-catalog">`pg_catalog`</InternalLink> and <InternalLink path="architecture/glossary#spatial-system-tables">`pg_extension`</InternalLink> are provided for compatibility with PostgreSQL.

To create a new database, use a <InternalLink path="create-database">`CREATE DATABASE`</InternalLink> statement. To create a new schema, use a <InternalLink path="create-schema">`CREATE SCHEMA`</InternalLink> statement. The list of all databases can be obtained with <InternalLink path="show-databases">`SHOW DATABASES`</InternalLink>. The list of all schemas for a given database can be obtained with <InternalLink path="show-schemas">`SHOW SCHEMAS`</InternalLink>. The list of all objects for a given schema can be obtained with other `SHOW` statements.

### Migrating namespaces from previous versions of CockroachDB

In CockroachDB versions \< v20.2, <InternalLink path="create-schema">user-defined schemas</InternalLink> 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 <InternalLink path="create-database">database</InternalLink> 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](https://www.postgresql.org/docs/current/ddl-schemas).

If you are upgrading to v24.1, take any combination of the following actions after the upgrade is complete:

* <InternalLink path="create-schema">Create new schemas</InternalLink> in databases on your cluster. After the schemas are created, use <InternalLink path="alter-table#rename-to">`ALTER TABLE... RENAME`</InternalLink>, <InternalLink path="alter-sequence">`ALTER SEQUENCE... RENAME`</InternalLink>, <InternalLink path="alter-type">`ALTER TYPE... RENAME`</InternalLink>, or <InternalLink path="alter-view">`ALTER VIEW... RENAME`</InternalLink> statements to move objects between databases as needed. To move objects between schemas, use <InternalLink path="alter-table#set-schema">`ALTER TABLE... SET SCHEMA`</InternalLink>, <InternalLink path="alter-sequence">`ALTER SEQUENCE... SET SCHEMA`</InternalLink>, or <InternalLink path="alter-view">`ALTER VIEW... SET SCHEMA`</InternalLink>.
* If your cluster contains cross-database references (e.g., a cross-database foreign key reference, or a cross-database view reference), use the relevant <InternalLink path="alter-table">`ALTER TABLE`</InternalLink>, <InternalLink path="alter-sequence">`ALTER SEQUENCE`</InternalLink>, <InternalLink path="alter-type">`ALTER TYPE`</InternalLink>, or <InternalLink path="alter-view">`ALTER VIEW`</InternalLink> statements 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 <InternalLink path="foreign-key">foreign keys</InternalLink>, <InternalLink path="views">views</InternalLink>, and <InternalLink path="create-sequence">sequence ownership</InternalLink>.

## 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:

1. If the name already fully specifies the database and schema, use that information.
2. 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](#current-database) and [current schema](#current-schema). If that fails, try to find the object in the `public` schema of a database with the prefix name.
3. If the name has no prefix, use the [search path](#search-path) with the [current database](#current-database).

Similarly, the rules to decide the full name of a new object are as follows:

1. If the name already fully specifies the database and schema, use that.
2. 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 `public` schema in the database with the prefix name.
3. If the name has no prefix, use the [current schema](#current-schema) in the [current database](#current-database).

## Parameters for name resolution

### Current database

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 view the current value of the `database` session variable with <InternalLink path="show-vars">`SHOW database`</InternalLink> and change it with <InternalLink path="set-vars">`SET database`</InternalLink>.
* You can inspect the list of valid database names that can be specified in `database` with <InternalLink path="show-databases">`SHOW DATABASES`</InternalLink>.
* For client apps that connect to CockroachDB using a URL of the form `postgres://...`, the initial value of the `database` session variable can be set using the path component of the URL. For example, `postgres://node/mydb` sets `database` to `mydb` when the connection is established.

### Search path

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](#current-schema) where new objects are created.

* You can set the current search path with <InternalLink path="set-vars">`SET search_path`</InternalLink> and inspected it with <InternalLink path="show-vars">`SHOW search_path`</InternalLink>.
* You can inspect the list of valid schemas that can be listed in `search_path` with <InternalLink path="show-schemas">`SHOW SCHEMAS`</InternalLink>.
* By default, the search path contains `$user`, `public`, `pg_catalog`, and `pg_extension`. For compatibility with PostgreSQL, `pg_catalog` is forced to be present in `search_path` at all times, even when not specified with `SET search_path`.
* To mimic the behavior in PostgreSQL, CockroachDB will attempt a resolution to `pg_extension` prior to attempting `public`.

### Current schema

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 `current_schema()`.

## Index name resolution

CockroachDB supports the following ways to specify an index name for statements that require one (e.g., <InternalLink path="drop-index">`DROP INDEX`</InternalLink>, <InternalLink path="alter-index">`ALTER INDEX... RENAME`</InternalLink>, etc.):

1. Index names are resolved relative to a table name using the `@` character, e.g., `DROP INDEX tbl@idx;`. This is the default and most common syntax.
2. Index names are resolved by searching all tables in the current schema to find a table with an index named `idx`, e.g., `DROP INDEX idx;` or (with optional schema prefix) `DROP INDEX public.idx;`. This syntax is necessary for PostgreSQL compatibility because PostgreSQL index names live in the schema namespace such that e.g., `public.idx` will resolve to the index `idx` of 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](#how-name-resolution-works) as other objects.

## Examples

The examples below use the following logical schema as a starting point:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DATABASE mydb;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE mydb.mytable(x INT);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET database = mydb;
```

### Lookup with unqualified names

An unqualified name is a name with no prefix, that is, a simple identifier.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 `mydb.public.mytable`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET database = system;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM mytable;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 `mydb.public.mytable`.

### 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 <InternalLink path="information-schema">Information Schema</InternalLink>. Because `information_schema` is not in the search path by default, all queries that need to access it must mention it explicitly.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM mydb.information_schema.schemata; -- valid
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM information_schema.schemata; -- valid; uses mydb implicitly
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET search_path = public, information_schema;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 `information_schema`, `public`, `pg_catalog`, <InternalLink path="architecture/glossary#spatial-system-tables">`pg_extension`</InternalLink>, or `crdb_internal`.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DATABASE public;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET database = mydb;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE public.mypublictable (x INT);
```

The <InternalLink path="create-table">`CREATE TABLE`</InternalLink> 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 `mypublictable` becomes `mydb.public.mypublictable`. The table is created in database `mydb`.

To create the table in database `public`, one would instead use a fully qualified name, as follows:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DATABASE public;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE public.public.mypublictable (x INT);
```

### Preloaded databases

New clusters and existing clusters <InternalLink path="upgrade-cockroach-version">upgraded</InternalLink> to v24.1 or later will include auto-generated databases, with the following purposes:

* The empty `defaultdb` database is used if a client does not specify a database in the <InternalLink path="connection-parameters">connection parameters</InternalLink>.
* The `movr` database contains data about users, vehicles, and rides for the vehicle-sharing app <InternalLink path="movr">MovR</InternalLink> (only when the cluster is started using the <InternalLink path="cockroach-demo">`demo` command</InternalLink> ).
* The empty `postgres` database is provided for compatibility with PostgreSQL client applications that require it.
* The `system` database contains CockroachDB metadata and is read-only.

All databases except for the `system` database can be <InternalLink path="drop-database">deleted</InternalLink> if they are not needed.

<Danger>
  Do not query the `system` database directly. Instead, use objects within the <InternalLink path="system-catalogs">system catalogs</InternalLink>.
</Danger>

## See also

* <InternalLink path="create-schema">`CREATE SCHEMA`</InternalLink>
* <InternalLink path="set-vars">`SET`</InternalLink>
* <InternalLink path="show-vars">`SHOW`</InternalLink>
* <InternalLink path="show-databases">`SHOW DATABASES`</InternalLink>
* <InternalLink path="show-schemas">`SHOW SCHEMAS`</InternalLink>
* <InternalLink path="information-schema">Information Schema</InternalLink>
* <InternalLink path="keywords-and-identifiers">Keywords and Identifiers</InternalLink>