SQL Statements

On this page Carat arrow pointing down
Warning:
As of April 12, 2019, CockroachDB v1.1 is no longer supported. 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 SELECT statement.
DELETE Delete specific rows from a table.
EXPLAIN View debugging and analysis details for a SELECT, INSERT, UPDATE, or DELETE statement.
IMPORT New in v1.1: Import an entire table's data via CSV files.
INSERT Insert rows into a table.
SELECT Select rows from a table.
SHOW TRACE New in v1.1: Execute a statement and then return a trace of its actions through all of CockroachDB's software layers.
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.

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 TABLE Apply a schema change to a table.
ALTER VIEW Rename a view.
CREATE DATABASE Create a new database.
CREATE INDEX Create an index for a table.
CREATE TABLE Create a new table in a database.
CREATE TABLE AS Create a new table in a database using the results from a SELECT statement.
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 TABLE Remove a table.
DROP VIEW Remove a view.
RENAME COLUMN Rename a column in a table.
RENAME DATABASE Rename a database.
RENAME INDEX Rename an index for a table.
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 TABLE View the CREATE TABLE statement that would create a carbon copy of the specified table.
SHOW CREATE VIEW View the CREATE VIEW statement that would create a carbon copy of the specified view.
SHOW DATABASES List databases in the cluster.
SHOW INDEX View index information for a table.
SHOW TABLES List tables in a database.

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 isolation level or priority for the session or for an individual transaction.
SHOW View the current transaction settings.

Privilege Management Statements

Statement Usage
CREATE USER Create SQL users, which lets you control privileges on your databases and tables.
DROP USER New in v1.1: Remove SQL users.
GRANT Grant privileges to users.
REVOKE Revoke privileges from users.
SHOW GRANTS View privileges granted to users.
SHOW USERS Lists the users for all databases.

Session Management Statements

Statement Usage
RESET New in v1.1: Reset a session variable to its default value.
SET Set a current session variable.
SET TRANSACTION Set the isolation level or priority for an individual transaction.
SHOW List the current session or transaction settings.

Cluster Management Statements

Statement Usage
RESET CLUSTER SETTING New in v1.1: 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.

Query Management Statements

Statement Usage
CANCEL QUERY New in v1.1: Cancel a running SQL query.
SHOW QUERIES New in v1.1: List details about current active SQL queries.

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 New in v1.1: Cancel a BACKUP or RESTORE job.
PAUSE JOB New in v1.1: Pause a BACKUP or RESTORE job.
RESUME JOB New in v1.1: Resume paused BACKUP or RESTORE jobs.
SHOW JOBS New in v1.1: View information on jobs.

Backup & 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 New in v1.1: List the contents of a backup.

Yes No
On this page

Yes No