SQL Statements

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

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	New in v2.1: 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	New in v2.1: 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	New in v2.1: Change a column's data type.
ALTER USER	Add or change a user's password.
ALTER VIEW	Rename a view.
CONFIGURE ZONE	New in v2.1: 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 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 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	New in v2.1: 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 retryable 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 cockroach_restart 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	New in v2.1: 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	New in v2.1: 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	New in v2.1: Execute the query and generate a physical query plan with execution statistics.
SHOW STATISTICS	New in v2.1: 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.

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)

New in v2.1: Change data capture (CDC) provides row-level change feeds into Apache Kafka for downstream processing.

Statement	Usage
CREATE CHANGEFEED	Create a new changefeed, which provides row-level change subscriptions.