Learn CockroachDB SQL

On this page Carat arrow pointing down

This page walks you through some of the most essential CockroachDB SQL statements. For a complete list and related details, see SQL Statements.

Note:
CockroachDB aims to provide standard SQL with extensions, but some standard SQL functionality is not yet available. See our SQL Feature Support page for more details.

Create a Database

CockroachDB comes with a single default system database, which contains CockroachDB metadata and is read-only. To create a new database, use CREATE DATABASE followed by a database name:

icon/buttons/copy
CREATE DATABASE bank;

Database names must follow these identifier rules. To avoid an error in case the database already exists, you can include IF NOT EXISTS:

icon/buttons/copy
CREATE DATABASE IF NOT EXISTS bank;

When you no longer need a database, use DROP DATABASE followed by the database name to remove the database and all its objects:

icon/buttons/copy
DROP DATABASE bank;

Show Databases

To see all databases, use the SHOW DATABASES statement:

icon/buttons/copy
SHOW DATABASES;
+--------------------+
|      Database      |
+--------------------+
| bank               |
| system             |
+--------------------+
(2 rows)

Set the Default Database

To set the default database, use the SET statement:

icon/buttons/copy
SET DATABASE = bank;

When working with the default database, you do not need to reference it explicitly in statements. To see which database is currently the default, use the SHOW DATABASE statement (note the singular form):

icon/buttons/copy
SHOW DATABASE;
+----------+
| database |
+----------+
| bank     |
+----------+
(1 row)

Create a Table

To create a table, use CREATE TABLE followed by a table name, the column names, and the data type and constraint, if any, for each column:

icon/buttons/copy
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL
);

Table and column names must follow these rules. Also, when you do not explicitly define a primary key, CockroachDB will automatically add a hidden rowid column as the primary key.

To avoid an error in case the table already exists, you can include IF NOT EXISTS:

icon/buttons/copy
CREATE TABLE IF NOT EXISTS accounts (
    id INT PRIMARY KEY,
    balance DECIMAL
);

To show all of the columns from a table, use SHOW COLUMNS FROM followed by the table name:

icon/buttons/copy
SHOW COLUMNS FROM accounts;
+---------+---------+-------+---------+-----------+
|  Field  |  Type   | Null  | Default |  Indices  |
+---------+---------+-------+---------+-----------+
| id      | INT     | false | NULL    | {primary} |
| balance | DECIMAL | true  | NULL    | {}        |
+---------+---------+-------+---------+-----------+
(2 rows)

When you no longer need a table, use DROP TABLE followed by the table name to remove the table and all its data:

icon/buttons/copy
DROP TABLE accounts;

Show Tables

To see all tables in the active database, use the SHOW TABLES statement:

icon/buttons/copy
SHOW TABLES;
+----------+
|  Table   |
+----------+
| accounts |
| users    |
+----------+
(2 rows)

To view tables in a database that's not active, use SHOW TABLES FROM followed by the name of the database:

icon/buttons/copy
SHOW TABLES FROM animals;
+-----------+
|   Table   |
+-----------+
| aardvarks |
| elephants |
| frogs     |
| moles     |
| pandas    |
| turtles   |
+-----------+
(6 rows)

Insert Rows into a Table

To insert a row into a table, use INSERT INTO followed by the table name and then the column values listed in the order in which the columns appear in the table:

icon/buttons/copy
INSERT INTO accounts VALUES (1, 10000.50);

If you want to pass column values in a different order, list the column names explicitly and provide the column values in the corresponding order:

icon/buttons/copy
INSERT INTO accounts (balance, id) VALUES
    (25000.00, 2);

To insert multiple rows into a table, use a comma-separated list of parentheses, each containing column values for one row:

icon/buttons/copy
INSERT INTO accounts VALUES
    (3, 8100.73),
    (4, 9400.10);

Default values are used when you leave specific columns out of your statement, or when you explicitly request default values. For example, both of the following statements would create a row with balance filled with its default value, in this case NULL:

icon/buttons/copy
INSERT INTO accounts (id) VALUES
    (5);
icon/buttons/copy
INSERT INTO accounts (id, balance) VALUES
    (6, DEFAULT);
icon/buttons/copy
SELECT * FROM accounts WHERE id in (5, 6);
+----+---------+
| id | balance |
+----+---------+
|  5 | NULL    |
|  6 | NULL    |
+----+---------+
(2 rows)

Create an Index

Indexes help locate data without having to look through every row of a table. They're automatically created for the primary key of a table and any columns with a Unique constraint.

To create an index for non-unique columns, use CREATE INDEX followed by an optional index name and an ON clause identifying the table and column(s) to index. For each column, you can choose whether to sort ascending (ASC) or descending (DESC).

icon/buttons/copy
CREATE INDEX balance_idx ON accounts (balance DESC);

You can create indexes during table creation as well; just include the INDEX keyword followed by an optional index name and the column(s) to index:

icon/buttons/copy
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL,
    INDEX balance_idx (balance)
);

Show Indexes on a Table

To show the indexes on a table, use SHOW INDEX FROM followed by the name of the table:

icon/buttons/copy
SHOW INDEX FROM accounts;
+----------+-------------+--------+-----+---------+-----------+---------+----------+
|  Table   |    Name     | Unique | Seq | Column  | Direction | Storing | Implicit |
+----------+-------------+--------+-----+---------+-----------+---------+----------+
| accounts | primary     | true   |   1 | id      | ASC       | false   | false    |
| accounts | balance_idx | false  |   1 | balance | DESC      | false   | false    |
| accounts | balance_idx | false  |   2 | id      | ASC       | false   | true     |
+----------+-------------+--------+-----+---------+-----------+---------+----------+
(3 rows)

Query a Table

To query a table, use SELECT followed by a comma-separated list of the columns to be returned and the table from which to retrieve the data:

icon/buttons/copy
SELECT balance FROM accounts;
+----------+
| balance  |
+----------+
| 10000.50 |
| 25000.00 |
|  8100.73 |
|  9400.10 |
| NULL     |
| NULL     |
+----------+
(6 rows)

To retrieve all columns, use the * wildcard:

icon/buttons/copy
SELECT * FROM accounts;
+----+----------+
| id | balance  |
+----+----------+
|  1 | 10000.50 |
|  2 | 25000.00 |
|  3 |  8100.73 |
|  4 |  9400.10 |
|  5 | NULL     |
|  6 | NULL     |
+----+----------+
(6 rows)

To filter the results, add a WHERE clause identifying the columns and values to filter on:

icon/buttons/copy
SELECT id, balance FROM accounts WHERE balance > 9000;
+----+---------+
| id | balance |
+----+---------+
|  2 |   25000 |
|  1 | 10000.5 |
|  4 |  9400.1 |
+----+---------+
(3 rows)

To sort the results, add an ORDER BY clause identifying the columns to sort by. For each column, you can choose whether to sort ascending (ASC) or descending (DESC).

icon/buttons/copy
SELECT id, balance FROM accounts ORDER BY balance DESC;
+----+---------+
| id | balance |
+----+---------+
|  2 |   25000 |
|  1 | 10000.5 |
|  4 |  9400.1 |
|  3 | 8100.73 |
|  5 | NULL    |
|  6 | NULL    |
+----+---------+
(6 rows)

Update Rows in a Table

To update rows in a table, use UPDATE followed by the table name, a SET clause identifying the columns to update and their new values, and a WHERE clause identifying the rows to update:

icon/buttons/copy
UPDATE accounts SET balance = balance - 5.50 WHERE balance < 10000;
icon/buttons/copy
SELECT * FROM accounts;
+----+----------+
| id | balance  |
+----+----------+
|  1 | 10000.50 |
|  2 | 25000.00 |
|  3 |  8095.23 |
|  4 |  9394.60 |
|  5 | NULL     |
|  6 | NULL     |
+----+----------+
(6 rows)

If a table has a primary key, you can use that in the WHERE clause to reliably update specific rows; otherwise, each row matching the WHERE clause is updated. When there's no WHERE clause, all rows in the table are updated.

Delete Rows in a Table

To delete rows from a table, use DELETE FROM followed by the table name and a WHERE clause identifying the rows to delete:

icon/buttons/copy
DELETE FROM accounts WHERE id in (5, 6);
icon/buttons/copy
SELECT * FROM accounts;
+----+----------+
| id | balance  |
+----+----------+
|  1 | 10000.50 |
|  2 | 25000.00 |
|  3 |  8095.23 |
|  4 |  9394.60 |
+----+----------+
(4 rows)

Just as with the UPDATE statement, if a table has a primary key, you can use that in the WHERE clause to reliably delete specific rows; otherwise, each row matching the WHERE clause is deleted. When there's no WHERE clause, all rows in the table are deleted.

What's Next?


Yes No
On this page

Yes No