ON COMMIT DELETE ROWS or ON COMMIT DROP. Refer to Control what happens at commit.
To create a temp table, add TEMP/TEMPORARY to a CREATE TABLE or CREATE TABLE AS statement. For full syntax details, see the CREATE TABLE and CREATE TABLE AS pages. For example usage, see Examples.
By default, temp tables are disabled in CockroachDB. To enable temp tables, set the
experimental_enable_temp_tables session variable to on.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, unless they are created with
ON COMMIT DELETE ROWS(all rows are removed whenever a transaction commits) orON COMMIT DROP(the table is dropped when the transaction that created it commits). - Temp tables can reference persistent tables, but persistent tables cannot reference temp tables.
- Temp tables cannot be converted to persistent tables.
- Temp tables can be created and used within a PL/pgSQL stored procedure body when the
sql.procedures.plpgsql.late_binding.enabledcluster setting is enabled. A temp table created in a procedure is added to the session’s temporary schema. Refer to Create temporary tables. - For PostgreSQL compatibility, CockroachDB supports the clauses
ON COMMIT PRESERVE ROWS(the default behavior),ON COMMIT DELETE ROWS, andON COMMIT DROPat the end ofCREATE TEMP TABLEstatements. Refer to Control what happens at commit.
sql.temp_object_cleaner.cleanup_interval cluster setting.
Control what happens at commit
By default, a temp table and its rows persist until the end of the session. To scope a temp table or its rows to a transaction, add one of the following clauses to the end of theCREATE TEMP TABLE or CREATE TEMP TABLE AS statement:
ON COMMIT PRESERVE ROWS: The default behavior. The table and its rows persist when a transaction commits.ON COMMIT DELETE ROWS: All rows are removed from the table and its indexes whenever a transaction in the session commits, including transactions that did not access the table. This also applies to implicit transactions. AnINSERTrun as a standalone statement commits immediately and removes its own rows, so aSELECTrun as the next statement returns no rows. To keep the rows between statements, run them in the same explicit transaction. Rows are removed without firing row-levelDELETEtriggers, and sequences owned by the table are not reset.ON COMMIT DROP: The table is dropped when the transaction that created it commits. Objects that depend on the table, such as views and the sequences owned by the table, are dropped with it, as withDROP TABLE ... CASCADE. Outside of a transaction block, the table is dropped as soon as theCREATE TEMP TABLEstatement commits, and CockroachDB returns aNOTICE.
ON COMMIT clause is accepted only on temp tables. On a persistent table, the statement fails with error code 42P16.
A rollback runs no ON COMMIT action. ROLLBACK TO SAVEPOINT does not cancel a pending action, unless it rolls back the CREATE TEMP TABLE statement itself.
For examples, refer to Clear a temp table at each commit and Drop a temp table at commit.
Known limitations
- When the
autocommit_before_ddlsession variable ison,CREATE TEMP TABLE ... ON COMMIT DROPdoes not commit the open transaction.CREATE TEMP TABLE ... ON COMMIT DELETE ROWSand any other schema change in the same transaction, such asCREATE INDEXon the new table, still commit the transaction before they run. This drops anON COMMIT DROPtable before later statements in the transaction can use it. To run other schema changes in the same transaction, setautocommit_before_ddltoofffor the session. - Under
READ COMMITTEDorREPEATABLE READisolation,CREATE TEMP TABLE ... ON COMMIT DROPmust be the first statement in the transaction. The transaction is then upgraded toSERIALIZABLE. After other statements, it fails with error code0A000. - A foreign key that references an
ON COMMIT DELETE ROWStable from a table that is not also created withON COMMIT DELETE ROWSis rejected with error code0A000when the transaction that adds the foreign key commits, and the transaction is rolled back. - When the
copy_from_atomic_enabledsession variable isoff, aCOPYstatement still runs in a single transaction while the session has a temp table with a pendingON COMMIT DELETE ROWSorON COMMIT DROPaction, and CockroachDB returns aNOTICE.
Performance considerations
- Temporary tables are not optimized for performance. They use the same underlying mechanisms as “regular” tables, and may be slower than expected compared to alternatives such as common table expressions (CTEs).
- Avoid patterns that create and drop very large numbers of temp tables in rapid succession. Creating and dropping large numbers of temp tables can enqueue many schema change GC jobs and degrade overall cluster performance.
- Prefer CTEs for intermediate results where possible. If you do use temp tables instead of CTEs, consider reusing a small set of temp tables with
TRUNCATEinstead of repeatedly creating and dropping new ones. Always test both approaches for your workload.
Temporary schemas
Temp tables are not part of thepublic 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, temporary views, and temporary sequences in the current session for a database. In a session, you can reference the session’s temporary schema as pg_temp.
Because the
SHOW TABLES 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.Examples
For intermediate results, consider using common table expressions (CTEs) instead of temp tables. For more information, see Performance considerations.
experimental_enable_temp_tables to on:
Create a temp table
SHOW CREATE to view temp tables:
pg_temp schema, use SHOW SCHEMAS:
Create a temp table that references another temp table
To create another temp table that referencesusers:
Show all temp tables in a session
To show all temp tables in a session’s temporary schema, useSHOW TABLES FROM pg_temp:
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 information_schema and pg_catalog schemas.
For example, the information_schema.tables table includes information about all tables in all schemas in all databases, including temp tables:
Clear a temp table at each commit
The following statements create a temp table whose rows are removed whenever a transaction in the session commits. Create the table outside of a transaction. Withautocommit_before_ddl enabled, creating the table inside BEGIN ... COMMIT would commit the transaction first. Refer to Known limitations.

