SQL Statements

SQL Statements

CockroachDB supports the following SQL statements. Click a statement for more details.

Tip:

In the built-in SQL shell, use \h [statement] to get inline help about a specific statement.

Data manipulation statements

Statement	Usage
`CREATE TABLE AS`	Create a new table in a database using the results from a selection query.
`DELETE`	Delete specific rows from a table.
`EXPORT`	Export an entire table's data, or the results of a `SELECT` statement, to CSV files. This statement is available only to enterprise users.
`IMPORT`	Import an entire table's data via CSV files.
`INSERT`	Insert rows into a table.
`SELECT`	Select specific rows and columns from a table and optionally compute derived values.
`TABLE`	Select all rows and columns from a table.
`TRUNCATE`	Delete all rows from specified tables.
`UPDATE`	Update rows in a table.
`UPSERT`	Insert rows that do not violate uniqueness constraints; update rows that do.
`VALUES`	Return rows containing specific values.

Data definition statements

Statement	Usage
`ADD COLUMN`	Add columns to a table.
`ADD CONSTRAINT`	Add a constraint to a column.
`ALTER COLUMN`	Change a column's Default constraint or drop the `NOT NULL` constraint.
`ALTER DATABASE`	Apply a schema change to a database.
`ALTER INDEX`	Apply a schema change to an index.
`ALTER RANGE`	Change an existing system range.
`ALTER SEQUENCE`	Apply a schema change to a sequence.
`ALTER TABLE`	Apply a schema change to a table.
`ALTER TYPE`	Change a column's data type.
`ALTER USER`	Add or change a user's password.
`ALTER VIEW`	Rename a view.
`COMMENT ON`	Associate a comment to a database, table, or column.
`CONFIGURE ZONE`	Add, modify, reset, and remove replication zones.
`CREATE DATABASE`	Create a new database.
`CREATE INDEX`	Create an index for a table.
`CREATE SEQUENCE`	Create a new sequence.
`CREATE TABLE`	Create a new table in a database.
`CREATE TABLE AS`	Create a new table in a database using the results from a selection query.
`CREATE VIEW`	Create a new view in a database.
`DROP COLUMN`	Remove columns from a table.
`DROP CONSTRAINT`	Remove constraints from a column.
`DROP DATABASE`	Remove a database and all its objects.
`DROP INDEX`	Remove an index for a table.
`DROP SEQUENCE`	Remove a sequence.
`DROP TABLE`	Remove a table.
`DROP VIEW`	Remove a view.
`EXPERIMENTAL_AUDIT`	Turn SQL audit logging on or off for a table.
`RENAME COLUMN`	Rename a column in a table.
`RENAME CONSTRAINT`	Rename a constraint on a column.
`RENAME DATABASE`	Rename a database.
`RENAME INDEX`	Rename an index for a table.
`RENAME SEQUENCE`	Rename a sequence.
`RENAME TABLE`	Rename a table or move a table between databases.
`SHOW COLUMNS`	View details about columns in a table.
`SHOW CONSTRAINTS`	List constraints on a table.
`SHOW CREATE`	View the `CREATE` statement for a table, view, or sequence.
`SHOW DATABASES`	List databases in the cluster.
`SHOW INDEX`	View index information for a table.
`SHOW SCHEMAS`	List the schemas in a database.
`SHOW SEQUENCES`	List the sequences in a database.
`SHOW TABLES`	List tables or views in a database or virtual schema.
`SHOW EXPERIMENTAL_RANGES`	Show range information about a specific table or index.
`SHOW ZONE CONFIGURATIONS`	List details about existing replication zones.
`SPLIT AT`	Force a key-value layer range split at the specified row in the table or index.
`VALIDATE CONSTRAINT`	Check whether values in a column match a constraint on the column.

Transaction management statements

Statement	Usage
`BEGIN`	Initiate a transaction.
`COMMIT`	Commit the current transaction.
`RELEASE SAVEPOINT`	When using the CockroachDB-provided function for client-side transaction retries, commit the transaction's changes once there are no retry errors.
`ROLLBACK`	Discard all updates made by the current transaction or, when using the CockroachDB-provided function for client-side transaction retries, rollback to the savepoint and retry the transaction.
`SAVEPOINT`	When using the CockroachDB-provided function for client-side transaction retries, start a retryable transaction.
`SET TRANSACTION`	Set the priority for the session or for an individual transaction.
`SHOW`	View the current transaction settings.

Access management statements

Statement	Usage
`CREATE ROLE`	Create SQL roles, which are groups containing any number of roles and users as members.
`CREATE USER`	Create SQL users, which lets you control privileges on your databases and tables.
`DROP ROLE`	Remove one or more SQL roles.
`DROP USER`	Remove one or more SQL users.
`GRANT <privileges>`	Grant privileges to users or roles.
`GRANT <roles>`	Add a role or user as a member to a role.
`REVOKE <privileges>`	Revoke privileges from users or roles.
`REVOKE <roles>`	Revoke a role or user's membership to a role.
`SHOW GRANTS`	View privileges granted to users.
`SHOW ROLES`	Lists the roles for all databases.
`SHOW USERS`	Lists the users for all databases.

Session management statements

Statement	Usage
`RESET`	Reset a session variable to its default value.
`SET`	Set a current session variable.
`SET TRANSACTION`	Set the priority for an individual transaction.
`SHOW TRACE FOR SESSION`	Return details about how CockroachDB executed a statement or series of statements recorded during a session.
`SHOW`	List the current session or transaction settings.

Cluster management statements

Statement	Usage
`RESET CLUSTER SETTING`	Reset a cluster setting to its default value.
`SET CLUSTER SETTING`	Set a cluster-wide setting.
`SHOW ALL CLUSTER SETTINGS`	List the current cluster-wide settings.
`SHOW SESSIONS`	List details about currently active sessions.
`CANCEL SESSION`	Cancel a long-running session.

Query management statements

Statement	Usage
`CANCEL QUERY`	Cancel a running SQL query.
`SHOW QUERIES`	List details about current active SQL queries.

Query planning statements

Statement	Usage
`CREATE STATISTICS`	Create table statistics for the cost-based optimizer to use.
`EXPLAIN`	View debugging and analysis details for a statement that operates over tabular data.
`EXPLAIN ANALYZE`	Execute the query and generate a physical query plan with execution statistics.
`SHOW STATISTICS`	List table statistics used by the cost-based optimizer.

Job management statements

Jobs in CockroachDB represent tasks that might not complete immediately, such as schema changes or enterprise backups or restores.

Statement	Usage
`CANCEL JOB`	Cancel a `BACKUP`, `RESTORE`, `IMPORT`, or `CHANGEFEED` job.
`PAUSE JOB`	Pause a `BACKUP`, `RESTORE`, `IMPORT`, or `CHANGEFEED` job.
`RESUME JOB`	Resume a paused `BACKUP`, `RESTORE`, `IMPORT`, or `CHANGEFEED` job.
`SHOW JOBS`	View information on jobs.

Backup and restore statements (Enterprise)

The following statements are available only to enterprise users.

Note:

For non-enterprise users, see Back up Data and Restore Data.

Statement	Usage
`BACKUP`	Create disaster recovery backups of databases and tables.
`RESTORE`	Restore databases and tables using your backups.
`SHOW BACKUP`	List the contents of a backup.

Changefeed statements (Enterprise)

Change data capture (CDC) provides row-level change feeds into Apache Kafka for downstream processing.