On this page
Warning:
As of November 18, 2022, CockroachDB v21.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 selection query. |
COPY FROM |
Copy data from a third-party client to a CockroachDB cluster. Note that CockroachDB currently only supports COPY FROM statements issued from third-party clients, for compatibility with PostgreSQL drivers and ORMs. COPY FROM statements cannot be issued from the cockroach SQL shell. To import data from files, we 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. Note that this statement requires an Enterprise license. |
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 definition statements
Statement | Usage |
---|---|
ADD COLUMN |
Add columns to a table. |
ADD REGION |
New in v21.1: Add a region to a database. Note that multi-region features require an Enterprise license. |
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 INDEX |
Apply a schema change to an index. |
ALTER PARTITION |
Configure the replication zone for a partition. Note that 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 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. |
CONVERT TO SCHEMA |
Convert a database to a schema. |
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 |
New in v21.1: Drop a region from a database. Note that multi-region features require an Enterprise license. |
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. Note that 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 |
New in v21.1: Assign a primary region to a multi-region database, or change an existing primary region. Note that 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 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 |
New in v21.1: 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. Note that partitioning requires an Enterprise license. |
SHOW REGIONS |
New in v21.1: List the cluster regions or database regions 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. |
Transaction management 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. |
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 |
Grant privileges to users or roles, or add a role or user as a member to a role. |
REASSIGN OWNED |
New in v21.1: 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. |
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 |
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 databases and tables. |
RESTORE |
Restore databases and tables using your backups. |
SHOW BACKUP |
List the contents of a backup. |
CREATE SCHEDULE FOR BACKUP |
Create a schedule for periodic backups. |
SHOW SCHEDULES |
View information on backup schedules. |
PAUSE SCHEDULES |
Pause backup schedules. |
RESUME SCHEDULES |
Resume paused backup schedules. |
DROP SCHEDULES |
Drop backup schedules. |
Changefeed statements (Enterprise)
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. |