On this page
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 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 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. |
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. |