SQL Statements

On this page Carat arrow pointing down

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
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 FUNCTION Modify a user-defined function.
ALTER INDEX Apply a schema change to an index.
ALTER PARTITION Configure the replication zone for a partition. Partitioning requires an Enterprise license.
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 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.
CREATE DATABASE Create a new database.
CREATE FUNCTION Create a user-defined function.
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 DATABASE Remove a database and all its objects.
DROP FUNCTION Remove a user-defined function from a database.
DROP INDEX Remove an index for a table.
DROP OWNED BY Drop all objects owned by and any grants on objects not owned by a role.
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.
REFRESH Refresh the stored query results of a materialized view.
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, function, sequence, table, or view.
SHOW DATABASES List databases in the cluster.
SHOW DEFAULT SESSION VARIABLES FOR ROLE List the values for updated session variables that are applied to a given user or role.
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 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.

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 INTO 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 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.
ALTER BACKUP SCHEDULE Modify an existing backup schedule.
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 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 (e.g, Kafka, cloud storage).
CREATE SCHEDULE FOR CHANGEFEED (Enterprise) Create a scheduled changefeed to export data out of CockroachDB using an initial scan. to a configurable sink (e.g, Kafka, cloud storage).
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 (Enterprise) Modify an existing changefeed.

External resource statements

Statement Usage
CREATE EXTERNAL CONNECTION Create an external connection, which represents a provider-specific URI, to interact with resources that are external from CockroachDB.
SHOW CREATE EXTERNAL CONNECTION Display the connection name and the creation statements for active external connections.
DROP EXTERNAL CONNECTION Drop an external connection.

Yes No
On this page

Yes No