This tutorial walks you through some of the most essential CockroachDB SQL statements, using the movr dataset.
For a complete list of supported SQL statements and related details, see SQL Statements.
For more information about the movr dataset and the MovR example application, see MovR: A Global Ride-sharing App.
Before you begin
Do one of the following:
Run
cockroach demoto start a temporary, in-memory cluster with themovrdataset preloaded:$ cockroach demoLoad the
movrdataset into a persistent local cluster and open an interactive SQL shell:- Start a secure or insecure local cluster.
Use
cockroach workloadto load themovrdataset:$ cockroach workload init movr 'postgresql://root@localhost:26257?sslcert=certs%2Fclient.root.crt&sslkey=certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=certs%2Fca.crt'$ cockroach workload init movr 'postgresql://root@localhost:26257?sslmode=disable'Use
cockroach sqlto open an interactive SQL shell and setmovras the current database:$ cockroach sql --certs-dir=certs --host=localhost:26257> USE movr;$ cockroach sql --insecure --host=localhost:26257> USE movr;
Show tables
To see all tables in the active database, use the SHOW TABLES statement or the \dt shell command:
> SHOW TABLES;
table_name
+----------------------------+
promo_codes
rides
user_promo_codes
users
vehicle_location_histories
vehicles
(6 rows)
Create a table
Suppose that you want MovR to offer ride-sharing services, in addition to vehicle-sharing services. You'll need to add a table for drivers to the movr database. 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:
> CREATE TABLE drivers (
id UUID NOT NULL,
city STRING NOT NULL,
name STRING,
dl STRING UNIQUE,
address STRING,
CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);
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:
> CREATE TABLE IF NOT EXISTS drivers (
id UUID NOT NULL,
city STRING NOT NULL,
name STRING,
dl STRING UNIQUE,
address STRING,
CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);
To show all of the columns from a table, use the SHOW COLUMNS FROM <table> statement or the \d <table> shell command:
> SHOW COLUMNS FROM drivers;
column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
id | UUID | false | NULL | | {primary} | false
city | STRING | false | NULL | | {primary} | false
name | STRING | true | NULL | | {} | false
dl | STRING | true | NULL | | {} | false
address | STRING | true | NULL | | {} | false
(5 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:
> INSERT INTO drivers VALUES
('c28f5c28-f5c2-4000-8000-000000000026', 'new york', 'Petee', 'ABC-1234', '101 5th Ave');
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:
> INSERT INTO drivers (name, city, dl, address, id) VALUES
('Adam Driver', 'chicago', 'DEF-5678', '201 E Randolph St', '1eb851eb-851e-4800-8000-000000000006');
To insert multiple rows into a table, use a comma-separated list of parentheses, each containing column values for one row:
> INSERT INTO drivers VALUES
('8a3d70a3-d70a-4000-8000-00000000001b', 'seattle', 'Eric', 'GHI-9123', '400 Broad St'),
('9eb851eb-851e-4800-8000-00000000001f', 'new york', 'Harry Potter', 'JKL-456', '214 W 43rd St');
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 create a row where the name, dl, and address entries each contain their default value, in this case NULL:
> INSERT INTO drivers (id, city) VALUES
('70a3d70a-3d70-4400-8000-000000000016', 'chicago');
> INSERT INTO drivers (id, city, name, dl, address) VALUES
('b851eb85-1eb8-4000-8000-000000000024', 'seattle', DEFAULT, DEFAULT, DEFAULT);
> SELECT * FROM drivers WHERE id in ('70a3d70a-3d70-4400-8000-000000000016', 'b851eb85-1eb8-4000-8000-000000000024');
id | city | name | dl | address
+--------------------------------------+---------+------+------+---------+
70a3d70a-3d70-4400-8000-000000000016 | chicago | NULL | NULL | NULL
b851eb85-1eb8-4000-8000-000000000024 | seattle | NULL | NULL | 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).
> CREATE INDEX name_idx ON users (name 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:
> CREATE TABLE IF NOT EXISTS drivers (
id UUID NOT NULL,
city STRING NOT NULL,
name STRING,
dl STRING,
address STRING,
INDEX name_idx (name),
CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);
Show indexes on a table
To show the indexes on a table, use SHOW INDEX FROM followed by the name of the table:
> SHOW INDEX FROM users;
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
+------------+------------+------------+--------------+-------------+-----------+---------+----------+
users | primary | false | 1 | city | ASC | false | false
users | primary | false | 2 | id | ASC | false | false
users | name_idx | true | 1 | name | DESC | false | false
users | name_idx | true | 2 | city | ASC | false | true
users | name_idx | true | 3 | id | ASC | false | true
(5 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. You can also use the LIMIT clause to restrict the number of rows retrieved:
> SELECT name FROM users LIMIT 10;
name
+-------------------+
William Wood
Victoria Jennings
Tyler Dalton
Tony Ortiz
Tina Miller
Taylor Cunningham
Susan Morse
Steven Lara
Stephen Diaz
Sarah Wang DDS
(10 rows)
To retrieve all columns, use the * wildcard:
> SELECT * FROM users LIMIT 10;
id | city | name | address | credit_card
+--------------------------------------+-----------+--------------------+--------------------------------+-------------+
c28f5c28-f5c2-4000-8000-000000000026 | amsterdam | Maria Weber | 14729 Karen Radial | 5844236997
c7ae147a-e147-4000-8000-000000000027 | amsterdam | Tina Miller | 97521 Mark Extensions | 8880478663
cccccccc-cccc-4000-8000-000000000028 | amsterdam | Taylor Cunningham | 89214 Jennifer Well | 5130593761
d1eb851e-b851-4800-8000-000000000029 | amsterdam | Kimberly Alexander | 48474 Alfred Hollow | 4059628542
19999999-9999-4a00-8000-000000000005 | boston | Nicole Mcmahon | 11540 Patton Extensions | 0303726947
1eb851eb-851e-4800-8000-000000000006 | boston | Brian Campbell | 92025 Yang Village | 9016427332
23d70a3d-70a3-4800-8000-000000000007 | boston | Carl Mcguire | 60124 Palmer Mews Apt. 49 | 4566257702
28f5c28f-5c28-4600-8000-000000000008 | boston | Jennifer Sanders | 19121 Padilla Brooks Apt. 12 | 1350968125
80000000-0000-4000-8000-000000000019 | chicago | Matthew Clay | 49220 Lisa Junctions | 9132291015
851eb851-eb85-4000-8000-00000000001a | chicago | Samantha Coffey | 6423 Jessica Underpass Apt. 87 | 9437219051
(10 rows)
To filter the results, add a WHERE clause identifying the columns and values to filter on:
> SELECT id, name FROM users WHERE city = 'san francisco';
id | name
+--------------------------------------+------------------+
75c28f5c-28f5-4400-8000-000000000017 | William Wood
7ae147ae-147a-4000-8000-000000000018 | Alfred Garcia
80000000-0000-4000-8000-000000000019 | Matthew Clay
851eb851-eb85-4000-8000-00000000001a | Samantha Coffey
8a3d70a3-d70a-4000-8000-00000000001b | Jessica Martinez
(5 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).
> SELECT city, type, current_location FROM vehicles ORDER BY city, type DESC;
city | type | current_location
+---------------+------------+--------------------------------+
amsterdam | skateboard | 19202 Edward Pass
boston | scooter | 19659 Christina Ville
chicago | skateboard | 69721 Noah River
detroit | scooter | 43051 Jonathan Fords Suite 36
los angeles | skateboard | 49164 Anna Mission Apt. 38
minneapolis | scooter | 62609 Stephanie Route
minneapolis | scooter | 57637 Mitchell Shoals Suite 59
new york | skateboard | 64110 Richard Crescent
new york | scooter | 86667 Edwards Valley
paris | skateboard | 2505 Harrison Parkway Apt. 89
rome | bike | 64935 Matthew Flats Suite 55
san francisco | skateboard | 81472 Morris Run
san francisco | scooter | 91427 Steven Spurs Apt. 49
seattle | bike | 37754 Farmer Extension
washington dc | scooter | 47259 Natasha Cliffs
(15 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:
> UPDATE promo_codes SET (description, rules) = ('EXPIRED', '{"type": "percent_discount", "value": "0%"}') WHERE expiration_time < '2019-01-22 03:04:05+00:00';
> SELECT code, description, rules FROM promo_codes LIMIT 10;
code | description | rules
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+
a_blue_member | Yard send you end kitchen. High politics only support certainly. Reflect these agree travel bag myself. Month data magazine its trade water reality. | {"type": "percent_discount", "value": "10%"}
a_down_man | EXPIRED | {"type": "percent_discount", "value": "0%"}
ability_until_student | Set hot parent statement organization charge. Wide new bag easy note each trial. Act compare information marriage. Through they speech top. | {"type": "percent_discount", "value": "10%"}
about_mission_pull | Main serious education economy situation turn. Away senior realize evidence. Far himself against look. Husband skin pick within. Sense garden sister draw theory remain. | {"type": "percent_discount", "value": "10%"}
about_stuff_city | Skill sing rich glass store whatever teach. | {"type": "percent_discount", "value": "10%"}
accept_gas_hundred | Listen much get art popular. | {"type": "percent_discount", "value": "10%"}
accept_resource_something | EXPIRED | {"type": "percent_discount", "value": "0%"}
according_share_door | Region difference letter now huge next any. Nothing hotel gas election hospital hope give. Capital can address look. Window off beyond success couple PM as hair. Side who understand indeed future system vote. | {"type": "percent_discount", "value": "10%"}
account_interest_next | EXPIRED | {"type": "percent_discount", "value": "0%"}
act_even_camera | EXPIRED | {"type": "percent_discount", "value": "0%"}
(10 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:
> DELETE FROM promo_codes WHERE description = 'EXPIRED';
DELETE 669
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. We do not recommend using WHERE to delete all of the rows in a table. Instead, use TRUNCATE.
To delete a large number of rows, we recommend iteratively deleting batches of rows until all of the unwanted rows are deleted. For an example, see Batch deletes.
Remove a table
When you no longer need a table, use DROP TABLE followed by the table name to remove the table and all its data:
> DROP TABLE drivers;
What's next?
- Explore all SQL Statements
- Use the built-in SQL client to execute statements from a shell or directly from the command line
- Install the client driver for your preferred language and build an app
- Explore core CockroachDB features like automatic replication, rebalancing, and fault tolerance
Was this helpful?
