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

# Temporary Tables

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>;
};

export const version = "v23.2";

CockroachDB supports session-scoped temporary tables (also called "temp tables"). Unlike <InternalLink path="create-table">persistent tables</InternalLink>, temp tables can only be accessed from the session in which they were created, and they are dropped at the end of the session.

To create a temp table, add `TEMP`/`TEMPORARY` to a <InternalLink path="create-table">`CREATE TABLE`</InternalLink> or <InternalLink path="create-table-as">`CREATE TABLE AS`</InternalLink> statement. For full syntax details, see the <InternalLink path="create-table#synopsis">`CREATE TABLE`</InternalLink> and <InternalLink path="create-table-as#synopsis">`CREATE TABLE AS`</InternalLink> pages. For example usage, see [Examples](#examples).

<Note>
  **This feature is in <InternalLink path="cockroachdb-feature-availability">preview</InternalLink>** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).
</Note>

<Note>
  By default, temp tables are disabled in CockroachDB. To enable temp tables, set the `experimental_enable_temp_tables` <InternalLink path="set-vars">session variable</InternalLink> to `on`.
</Note>

CockroachDB also supports <InternalLink path="views#temporary-views">temporary views</InternalLink> and <InternalLink path="create-sequence#temporary-sequences">temporary sequences</InternalLink>.

## Details

* Temp tables are automatically dropped at the end of the session.
* A temp table can only be accessed from the session in which it was created.
* Temp tables persist across transactions in the same session.
* Temp tables can reference persistent tables, but persistent tables cannot reference temp tables.
* Temp tables cannot be converted to persistent tables.
* For [PostgreSQL compatibility](https://www.postgresql.org/docs/current/sql-createtable), CockroachDB supports the clause `ON COMMIT PRESERVE ROWS` at the end of `CREATE TEMP TABLE` statements. CockroachDB only supports session-scoped temp tables, and does not support the clauses `ON COMMIT DELETE ROWS` and `ON COMMIT DROP`, which are used to define transaction-scoped temp tables in PostgreSQL.

By default, every 30 minutes CockroachDB cleans up all temporary objects that are not tied to an active session. You can change how often the cleanup job runs with the `sql.temp_object_cleaner.cleanup_interval` <InternalLink path="cluster-settings">cluster setting</InternalLink>.

## Temporary schemas

Temp tables are not part of the `public` schema. Instead, when you create the first temp table for a session, CockroachDB generates a single temporary schema (`pg_temp_<id`) for all of the temp tables, <InternalLink path="views#temporary-views">temporary views</InternalLink>, and <InternalLink path="create-sequence#temporary-sequences">temporary sequences</InternalLink> in the current session for a database. In a session, you can reference the session's temporary schema as `pg_temp`.

<Note>
  Because the <InternalLink path="show-tables">`SHOW TABLES`</InternalLink> statement defaults to the `public` schema (which doesn't include temp tables), using `SHOW TABLES` without specifying a schema will not return any temp tables.
</Note>

## Examples

To use temp tables, you need to set `experimental_enable_temp_tables` to `on`:

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

### Create a temp table

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TEMP TABLE users (
        id UUID,
        city STRING,
        name STRING,
        address STRING,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);
```

You can use <InternalLink path="show-create">`SHOW CREATE`</InternalLink> to view temp tables:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE TABLE users;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  table_name |                create_statement
-------------+-------------------------------------------------
  users      | CREATE TEMP TABLE users (
             |     id UUID NOT NULL,
             |     city STRING NULL,
             |     name STRING NULL,
             |     address STRING NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     FAMILY "primary" (id, city, name, address)
             | )
(1 row)
```

To show the newly created `pg_temp` schema, use <InternalLink path="show-schemas">`SHOW SCHEMAS`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEMAS;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
           schema_name
---------------------------------
  crdb_internal
  information_schema
  pg_catalog
  pg_extension
  pg_temp_1602087923187609000_1
  public
(6 rows)
```

### Create a temp table that references another temp table

To create another temp table that references `users`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TEMP TABLE vehicles (
        id UUID NOT NULL,
        city STRING NOT NULL,
        type STRING,
        owner_id UUID,
        creation_time TIMESTAMP,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
        CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users(city, id)
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE TABLE vehicles;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  table_name |                                     create_statement
-------------+--------------------------------------------------------------------------------------------
  vehicles   | CREATE TEMP TABLE vehicles (
             |     id UUID NOT NULL,
             |     city STRING NOT NULL,
             |     type STRING NULL,
             |     owner_id UUID NULL,
             |     creation_time TIMESTAMP NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users(city, id),
             |     INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC),
             |     FAMILY "primary" (id, city, type, owner_id, creation_time)
             | )
(1 row)
```

### Show all temp tables in a session

To show all temp tables in a session's temporary schema, use `SHOW TABLES FROM pg_temp`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM pg_temp;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
           schema_name          | table_name | type  | estimated_row_count
--------------------------------+------------+-------+----------------------
  pg_temp_1602087923187609000_1 | users      | table |                   0
  pg_temp_1602087923187609000_1 | vehicles   | table |                   0
(2 rows)
```

You can also use the full name of the temporary schema in the `SHOW` statement (e.g., `SHOW TABLES FROM pg_temp_1602087923187609000_1`).

### Show temp tables in `information_schema`

Although temp tables are not included in the `public` schema, metadata for temp tables is included in the <InternalLink path="information-schema">`information_schema`</InternalLink> and `pg_catalog` schemas.

For example, the <InternalLink path="information-schema#tables">`information_schema.tables`</InternalLink> table includes information about all tables in all schemas in all databases, including temp tables:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM information_schema.tables WHERE table_schema='pg_temp_1602087923187609000_1';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   table_catalog |         table_schema          | table_name |   table_type    | is_insertable_into | version
-----------------+-------------------------------+------------+-----------------+--------------------+----------
  defaultdb      | pg_temp_1602087923187609000_1 | users      | LOCAL TEMPORARY | YES                |       2
  defaultdb      | pg_temp_1602087923187609000_1 | vehicles   | LOCAL TEMPORARY | YES                |       2
(2 rows)
```

### Cancel a session

If you end the session, all temp tables are lost.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW session_id;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
             session_id
------------------------------------
  15fd69f9831c1ed00000000000000001
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CANCEL SESSION '15fd69f9831c1ed00000000000000001';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ERROR: driver: bad connection
warning: connection lost!
opening new connection: all session settings will be lost
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE TABLE users;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ERROR: relation "users" does not exist
SQLSTATE: 42P01
```

## See also

* <InternalLink path="create-table">`CREATE TABLE`</InternalLink>
* <InternalLink path="create-table-as">`CREATE TABLE AS`</InternalLink>
* <InternalLink path="show-create">`SHOW CREATE TABLE`</InternalLink>
* <InternalLink path="views#temporary-views">Temporary views</InternalLink>
* <InternalLink path="create-sequence#temporary-sequences">Temporary sequences</InternalLink>.