SQL Statements

CockroachDB supports the following SQL statements.

In the cockroach SQL shell, use \h [statement] to get inline help about a statement.

Data definition statements

Statement	Usage
ADD COLUMN	Add a column to a table.
ADD REGION	Add a region to a database. Multi-region features require an Enterprise license.
ADD SUPER REGION	New in v22.1: Add a super region made up of a set of regions added with ADD REGION such that data from regional tables will be stored in only those regions.
ADD CONSTRAINT	Add a constraint to a column.
ALTER COLUMN	Change a column's default constraint, NOT NULL constraint, or data type.
ALTER DATABASE	Apply a schema change to a database.
ALTER DEFAULT PRIVILEGES	Change the default privileges for objects created by specific roles/users in the current database.
ALTER INDEX	Apply a schema change to an index.
ALTER PARTITION	Configure the replication zone for a partition. Partitioning requires an Enterprise license.
ALTER PRIMARY KEY	Change the primary key of a table.
ALTER RANGE	Configure the replication zone for a system range.
ALTER SCHEMA	Alter a user-defined schema.
ALTER SEQUENCE	Apply a schema change to a sequence.
ALTER SUPER REGION	New in v22.1: Alter an existing super region to include a different set of regions. A super region is made up of a set of regions added with ADD REGION such that data from regional tables will be stored in only those regions.
ALTER TABLE	Apply a schema change to a table.
ALTER TYPE	Modify a user-defined, enumerated data type.
ALTER USER	Add, change, or remove a user's password and to change the login privileges for a role.
ALTER ROLE	Add, change, or remove a role's password and to change the login privileges for a role.
ALTER VIEW	Apply a schema change to a view.
COMMENT ON	Associate a comment to a database, table, or column.
CONFIGURE ZONE	Add, modify, reset, or remove a replication zone for a database, table, index, partition, or system range.
CREATE DATABASE	Create a new database.
CREATE INDEX	Create an index for a table.
CREATE SCHEMA	Create a user-defined schema.
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 TYPE	Create a user-defined, enumerated data type.
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 REGION	Drop a region from a database. Multi-region features require an Enterprise license.
DROP SUPER REGION	New in v22.1: Drop a super region made up of a set of database regions.
DROP SCHEMA	Drop a user-defined schema.
DROP SEQUENCE	Remove a sequence.
DROP TABLE	Remove a table.
DROP TYPE	Remove a user-defined, enumerated data type.
DROP VIEW	Remove a view.
EXPERIMENTAL_AUDIT	Turn SQL audit logging on or off for a table.
PARTITION BY	Partition, re-partition, or un-partition a table or secondary index. Partitioning requires an Enterprise license.
REFRESH	Refresh the stored query results of a materialized view.
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 TABLE	Rename a table or move a table between databases.
SET SCHEMA	Change the schema of a table.
SET PRIMARY REGION	Assign a primary region to a multi-region database, or change an existing primary region. Multi-region features require an Enterprise license.
SHOW COLUMNS	View details about columns in a table.
SHOW CONSTRAINTS	List constraints on a table.
SHOW CREATE	View the CREATE statement for a database, table, view, or sequence.
SHOW DATABASES	List databases in the cluster.
SHOW ENUMS	List user-defined, enumerated data types in a database.
SHOW FULL TABLE SCANS	List recent queries that used a full table scan.
SHOW INDEX	View index information for a table or database.
SHOW LOCALITY	View the locality of the current node.
SHOW PARTITIONS	List partitions in a database. Partitioning requires an Enterprise license.
SHOW REGIONS	List the cluster regions or database regions in a multi-region cluster.
SHOW SUPER REGIONS	New in v22.1: List the super regions associated with a database in a multi-region cluster.
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 TYPES	List user-defined data types in a database.
SHOW RANGES	Show range information for all data in a table or index.
SHOW RANGE FOR ROW	Show range information for a single row in a table or index.
SHOW ZONE CONFIGURATIONS	List details about existing replication zones.
SPLIT AT	Force a range split at the specified row in the table or index.
UNSPLIT AT	Remove a range split enforcement at a specified row in the table or index.
VALIDATE CONSTRAINT	Check whether values in a column match a constraint on the column.

Data manipulation statements

Statement	Usage
CREATE TABLE AS	Create a new table in a database using the results from a selection query.
COPY FROM	Copy data from a third-party client to a CockroachDB cluster. For compatibility with PostgreSQL drivers and ORMs, CockroachDB supports COPY FROM statements issued only from third-party clients; you cannot issue COPY FROM statements from the cockroach SQL shell. To import data from files, use an IMPORT statement instead.
DELETE	Delete specific rows from a table.
EXPORT	Export an entire table's data, or the results of a SELECT statement, to CSV files.
IMPORT	Bulk-insert CSV data into a new table.
IMPORT INTO	Bulk-insert CSV data into an existing table.
INSERT	Insert rows into a table.
SELECT	Select specific rows and columns from a table and optionally compute derived values.
SELECT FOR UPDATE	Order transactions by controlling concurrent access to one or more rows of a table.
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 control 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	Grant privileges to users and roles, or add a role or user as a member to a role.
REASSIGN OWNED	Change the ownership of all database objects in the current database that are currently owned by a specific role or user.
REVOKE	Revoke privileges from users or roles, or 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.
SHOW DEFAULT PRIVILEGES	Show the default privileges for objects created by specific roles/users in the current database.

Transaction control statements

Statement	Usage
BEGIN	Initiate a transaction.
COMMIT	Commit the current transaction.
SAVEPOINT	Start a nested transaction.
RELEASE SAVEPOINT	Commit a nested transaction.
ROLLBACK TO SAVEPOINT	Roll back and restart the nested transaction started at the corresponding SAVEPOINT statement.
ROLLBACK	Roll back the current transaction and all of its nested transaction, discarding all transactional updates made by statements inside the transaction.
SET TRANSACTION	Set the priority for the session or for an individual transaction.
SHOW	View the current transaction settings.
SHOW TRANSACTIONS	View all currently active transactions across the cluster or on the local node.

Session management statements

Statement	Usage
RESET {session variable}	Reset a session variable to its default value.
SET {session variable}	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 {session variable}	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 STATEMENTS/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

Statement	Usage
BACKUP	Create disaster recovery backups of clusters, databases, and tables.
RESTORE	Restore clusters, databases, and tables using your backups.
SHOW BACKUP	List the contents of a backup.
CREATE SCHEDULE FOR BACKUP	Create a schedule for periodic backups. Core users can only use backup scheduling for full backups of clusters, databases, or tables. To use the other backup features, you need an Enterprise license.
SHOW SCHEDULES	View information on backup schedules.
PAUSE SCHEDULES	Pause backup schedules.
RESUME SCHEDULES	Resume paused backup schedules.
DROP SCHEDULES	Drop backup schedules.
ALTER BACKUP	New in v22.1: Add a new KMS encryption key to an encrypted backup. Adding new KMS encryption keys requires an Enterprise license.

Changefeed statements

Change data capture (CDC) provides an Enterprise and core version of row-level change subscriptions for downstream processing.

Statement	Usage
CREATE CHANGEFEED	(Enterprise) Create a new changefeed to stream row-level changes in a configurable format to a configurable sink (Kafka or a cloud storage sink).
EXPERIMENTAL CHANGEFEED FOR	(Core) Create a new changefeed to stream row-level changes to the client indefinitely until the underlying connection is closed or the changefeed is canceled.
ALTER CHANGEFEED	New in v22.1: Modify an existing changefeed. Modifying a changefeed requires an Enterprise license.