SQL Statements

On this page Carat arrow pointing down
Warning:
CockroachDB v2.1 is no longer supported as of April 30, 2020. For more details, refer to the Release Support Policy.

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

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)

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

Note:

CDC is an enterprise feature. There will be a core version in a future release.

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

Yes No
On this page

Yes No