cockroach sql

On this page

CockroachDB comes with a built-in client for executing SQL statements from an interactive shell or directly from the command line. To use this client, run the cockroach sql command as described below.

To exit the interactive shell, use \q, quit, exit, or Ctrl+D.

The output of cockroach sql when used non-interactively is part of a stable interface, and can be used programmatically, with the exception of informational output lines that begin with the hash symbol (#). Informational output can change from release to release, and should not be used programmatically.

Before you begin

• The role option of the user logging in must be LOGIN or SQLLOGIN, which are granted by default. If the user has been set to use the NOLOGIN role or the NOSQLLOGIN system privilege (or the legacy NOSQLLOGIN role), the user cannot log in using the SQL CLI with any authentication method.
• macOS users only: By default, macOS-based terminals do not enable handling of the Alt key modifier. This prevents access to many keyboard shortcuts in the unix shell and cockroach sql. See the section macOS terminal configuration below for details.

Synopsis

Start the interactive SQL shell:

$ cockroach sql <flags>

Execute SQL from the command line:

$ cockroach sql --execute="<sql statement>;<sql statement>" --execute="<sql-statement>" <flags>

$ echo "<sql statement>;<sql statement>" | cockroach sql <flags>

$ cockroach sql <flags> --file file-containing-statements.sql

Exit the interactive SQL shell:

> \q

> quit

> exit

> Ctrl+D

View help:

$ cockroach sql --help

Flags

The sql command supports the following types of flags:

• General Use
• Client Connection
• Logging

General

• To start an interactive SQL shell, run cockroach sql with all appropriate connection flags or use just the --url flag, which includes connection details.
• To execute SQL statements from the command line, use the --execute flag.

Client connection

See Client Connection Parameters for more details.

Logging

By default, this command logs messages to stderr. This includes events with WARNING severity and higher.

If you need to troubleshoot this command's behavior, you can customize its logging behavior.

Session and output types

cockroach sql exhibits different behaviors depending on whether or not the session is interactive and/or whether or not the session outputs on a terminal.

• A session is interactive when cockroach sql is invoked without the -e or -f flag, and the input is a terminal. In such cases:
  • The errexit option defaults to false.
  • The check_syntax option defaults to true if supported by the CockroachDB server (this is checked when the shell starts up).
  • Ctrl+C at the prompt will only terminate the shell if no other input was entered on the same line already.
  • The shell will attempt to set the safe_updates session variable to true on the server.
  • The shell continues to read input after the last command entered.
• A session outputs on a terminal when output is not redirected to a file. In such cases:
  • The --format flag and its corresponding display_format option default to table. These default to tsv otherwise.
  • The show_times option defaults to true.

When a session is both interactive and outputs on a terminal, cockroach sql also activates the interactive prompt with a line editor that can be used to modify the current line of input. Also, command history becomes active.

SQL shell

Welcome message

When the SQL shell connects (or reconnects) to a CockroachDB node, it prints a welcome text with some tips and CockroachDB version and cluster details:

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v23.1.29 (x86_64-apple-darwin17.7.0, built 2019/09/13 00:07:19, go1.12.6) (same version as client)
# Cluster ID: 7fb9f5b4-a801-4851-92e9-c0db292d03f1
#
# Enter \? for a brief introduction.
#
>

The Version and Cluster ID details are particularly noteworthy:

• When the client and server versions of CockroachDB are the same, the shell prints the Server version followed by (same version as client).
• When the client and server versions are different, the shell prints both the Client version and Server version. In this case, you may want to plan an upgrade of older client or server versions.

• Since every CockroachDB cluster has a unique ID, you can use the Cluster ID field to verify that your client is always connecting to the correct cluster.

  Note:

  For clusters deployed in CockroachDB Cloud, do not use the cluster ID printed in the welcome message to verify the cluster your client is connected to. Instead, use the ccloud cluster list command to list the ID of each cluster in your CockroachDB Cloud organization to which you have access. To learn more about the ccloud command, refer to Get Started with the ccloud CLI.

Commands

The following commands can be used within the interactive SQL shell:

Patterns

Commands use the SQL LIKE syntax for string pattern matching, not POSIX regular expressions.

For example to list all schemas that begin with the letter "p" you'd use the following pattern:

\dn p%

List of schemas:
      Name     | Owner
---------------+--------
  pg_catalog   | NULL
  pg_extension | NULL
  public       | admin
(3 rows)

Client-side options

• To view option descriptions and how they are currently set, use \set without any options.
• To enable or disable an option, use \set <option> <value> or \unset <option> <value>. You can also use the form <option>=<value>.
• If an option accepts a boolean value:
  • \set <option> without <value> is equivalent to \set <option> true, and \unset <option> without <value> is equivalent to \set <option> false.
  • on, yes, and 1 are aliases for true, and off, no, and 0 are aliases for false.

Customizing the prompt

The \set prompt1 option allows you to customize the interactive prompt in the SQL shell. Use the following prompt variables to set a custom prompt.

For example, to change the prompt to just the user, host, and database:

\set prompt1 %n@%m/%/

maxroach@blue-dog-595.g95.cockroachlabs.cloud/defaultdb>

Help

Within the SQL shell, you can get interactive help about statements and functions:

Examples

> \h UPDATE

Command:     UPDATE
Description: update rows of a table
Category:    data manipulation
Syntax:
UPDATE <tablename> [[AS] <name>] SET ... [WHERE <expr>] [RETURNING <exprs...>]

See also:
  SHOW TABLES
  INSERT
  UPSERT
  DELETE
  https://www.cockroachlabs.com/docs/v23.1/update.html

> \hf uuid_v4

Function:    uuid_v4
Category:    built-in functions
Returns a UUID.

Signature          Category
uuid_v4() -> bytes [ID Generation]

See also:
  https://www.cockroachlabs.com/docs/v23.1/functions-and-operators.html

Shortcuts

Note: macOS users may need to manually enable Alt-based shortcuts in their terminal configuration. See the section macOS terminal configuration below for details.

When searching for history entries, the following shortcuts are active:

Tab completion

New in v23.1: The SQL client offers context-sensitive tab completion when entering commands. Use the Tab key on your keyboard when entering a command to initiate the command completion interface. You can then navigate to database objects, keywords, and functions using the arrow keys. Press the Tab key again to select the object, function, or keyword from the command completion interface and return to the console.

macOS terminal configuration

In Apple Terminal:

1. Navigate to "Preferences", then "Profiles", then "Keyboard".
2. Enable the checkbox "Use Option as Meta Key".

In iTerm2:

1. Navigate to "Preferences", then "Profiles", then "Keys".
2. Select the radio button "Esc+" for the behavior of the Left Option Key.

Error messages and SQLSTATE codes

When CockroachDB encounters a SQL error, it returns the following information to the client (whether cockroach-sql or another client application):

1. An error message, prefixed with the "Severity" field of the PostgreSQL wire protocol. For example, ERROR: insert on table "shipments" violates foreign key constraint "fk_customers".
2. A 5-digit SQLSTATE error code as defined by the SQL standard. For example, SQLSTATE: 23503.

For example, the following query (taken from this example of adding multiple foreign key constraints) results in a SQL error, and returns both an error message and a SQLSTATE code as described above.

> INSERT INTO shipments (carrier, status, customer_id) VALUES ('DHL', 'At facility', 2000);

ERROR: insert on table "shipments" violates foreign key constraint "fk_customers"
SQLSTATE: 23503
DETAIL: Key (customer_id)=(2000) is not present in table "customers".

The SQLSTATE code in particular can be helpful in the following ways:

• It is a standard SQL error code that you can look up in documentation and search for on the web. For any given error state, CockroachDB tries to produce the same SQLSTATE code as PostgreSQL.
• If you are developing automation that uses the CockroachDB SQL shell, it is more reliable to check for SQLSTATE values than for error message strings, which are likely to change.

Examples

Start a SQL shell

In these examples, we connect a SQL shell to a secure cluster.

# Using the --url flag:
$ cockroach-sql \
--url="postgresql://maxroach@12.345.67.89:26257/critterdb?sslcert=certs/client.maxroach.crt&sslkey=certs/client.maxroach.key&sslmode=verify-full&sslrootcert=certs/ca.crt"

# Using standard connection flags:
$ cockroach-sql \
--certs-dir=certs \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

In these examples, we connect a SQL shell to an insecure cluster.

# Using the --url flag:
$ cockroach-sql \
--url="postgresql://maxroach@12.345.67.89:26257/critterdb?sslmode=disable"

# Using standard connection flags:
$ cockroach-sql --insecure \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

Execute SQL statement within the SQL shell

This example assumes that we have already started the SQL shell (see examples above).

> CREATE TABLE animals (id INT PRIMARY KEY DEFAULT unique_rowid(), name STRING);

> INSERT INTO animals (name) VALUES ('bobcat'), ('🐢 '), ('barn owl');

> SELECT * FROM animals;

          id         |   name
---------------------+-----------
  710907071259213825 | bobcat
  710907071259279361 | 🐢
  710907071259312129 | barn owl
(3 rows)

Execute SQL statements from the command line

In these examples, we use the --execute flag to execute statements from the command line:

# Statements with a single --execute flag:
$ cockroach-sql --insecure \
--execute="CREATE TABLE roaches (name STRING, country STRING); INSERT INTO roaches VALUES ('American Cockroach', 'United States'), ('Brownbanded Cockroach', 'United States')" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

CREATE TABLE
INSERT 2

# Statements with multiple --execute flags:
$ cockroach-sql --insecure \
--execute="CREATE TABLE roaches (name STRING, country STRING)" \
--execute="INSERT INTO roaches VALUES ('American Cockroach', 'United States'), ('Brownbanded Cockroach', 'United States')" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

CREATE TABLE
INSERT 2

In this example, we use the echo command to execute statements from the command line:

# Statements with the echo command:
$ echo "SHOW TABLES; SELECT * FROM roaches;" | cockroach-sql --insecure --user=maxroach --host=12.345.67.89 --database=critterdb

  schema_name | table_name | type  | owner | estimated_row_count | locality
--------------+------------+-------+-------+---------------------+-----------
  public      | animals    | table | demo  |                   0 | NULL
  public      | roaches    | table | demo  |                   0 | NULL
(2 rows)

          name          |    country
------------------------+----------------
  American Cockroach    | United States
  Brownbanded Cockroach | United States

Control how table rows are printed

In these examples, we show tables and special characters printed in various formats.

When the standard output is a terminal, --format defaults to table and tables are printed with ASCII art and special characters are not escaped for easy human consumption:

$ cockroach-sql --insecure \
--execute="SELECT '🐥' AS chick, '🐢' AS turtle" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

  chick | turtle
--------+---------
  🐥    | 🐢

However, you can explicitly set --format to another format (e.g., tsv or html):

$ cockroach-sql --insecure \
--format=tsv \
--execute="SELECT '🐥' AS chick, '🐢' AS turtle" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

chick   turtle
🐥    🐢

$ cockroach-sql --insecure \
--format=html \
--execute="SELECT '🐥' AS chick, '🐢' AS turtle" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

<table>
<thead><tr><th>row</th><th>chick</th><th>turtle</th></tr></thead>
<tbody>
<tr><td>1</td><td>🐥</td><td>🐢</td></tr>
</tbody>
<tfoot><tr><td colspan=3>1 row</td></tr></tfoot></table>

When piping output to another command or a file, --format defaults to tsv:

$ cockroach-sql --insecure \
--execute="SELECT '🐥' AS chick, '🐢' AS turtle" > out.txt \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

$ cat out.txt

1 row
chick   turtle
🐥    🐢

However, you can explicitly set --format to another format (e.g., table):

$ cockroach-sql --insecure \
--format=table \
--execute="SELECT '🐥' AS chick, '🐢' AS turtle" > out.txt \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

$ cat out.txt

  chick | turtle
--------+---------
  🐥    | 🐢
(1 row)

Show borders around the statement output within the SQL shell

To display outside and inside borders in the statement output, set the border SQL shell option to 3.

\set border=3
SELECT * FROM animals;

+--------------------+----------+
|         id         |   name   |
+--------------------+----------+
| 710907071259213825 | bobcat   |
+--------------------+----------+
| 710907071259279361 | 🐢       |
+--------------------+----------+
| 710907071259312129 | barn owl |
+--------------------+----------+

Make the output of SHOW statements selectable

To make it possible to select from the output of SHOW statements, set --format to raw:

$ cockroach-sql --insecure \
--format=raw \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

> SHOW CREATE customers;

# 2 columns
# row 1
## 14
test.customers
## 185
CREATE TABLE customers (
    id INT NOT NULL,
    email STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (id ASC),
    UNIQUE INDEX customers_email_key (email ASC),
    FAMILY "primary" (id, email)
)
# 1 row

When --format is not set to raw, you can use the display_format SQL shell option to change the output format within the interactive session:

> \set display_format raw

# 2 columns
# row 1
## 14
test.customers
## 185
CREATE TABLE customers (
  id INT NOT NULL,
  email STRING NULL,
  CONSTRAINT "primary" PRIMARY KEY (id ASC),
  UNIQUE INDEX customers_email_key (email ASC),
  FAMILY "primary" (id, email)
)
# 1 row

Execute SQL statements from a file

In this example, we show and then execute the contents of a file containing SQL statements.

$ cat statements.sql

CREATE TABLE roaches (name STRING, country STRING);
INSERT INTO roaches VALUES ('American Cockroach', 'United States'), ('Brownbanded Cockroach', 'United States');

$ cockroach-sql --insecure \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb \
-f statements.sql

CREATE TABLE
INSERT 2

Run external commands from the SQL shell

In this example, we use \! to look at the rows in a CSV file before creating a table and then using \| to insert those rows into the table.

> \! cat test.csv

12, 13, 14
10, 20, 30

> CREATE TABLE csv (x INT, y INT, z INT);

> \| IFS=","; while read a b c; do echo "insert into csv values ($a, $b, $c);"; done < test.csv;

> SELECT * FROM csv;

  x  | y  | z
-----+----+-----
  12 | 13 | 14
  10 | 20 | 30

In this example, we create a table and then use \| to programmatically insert values.

> CREATE TABLE for_loop (x INT);

> \| for ((i=0;i<10;++i)); do echo "INSERT INTO for_loop VALUES ($i);"; done

> SELECT * FROM for_loop;

  x
-----
  0
  1
  2
  3
  4
  5
  6
  7
  8
  9

Allow potentially unsafe SQL statements

The --safe-updates flag defaults to true. This prevents SQL statements that may have broad, undesired side effects. For example, by default, we cannot use DELETE without a WHERE clause to delete all rows from a table:

$ cockroach-sql --insecure --execute="SELECT * FROM db1.t1"

  id | name
-----+-------
   1 | a
   2 | b
   3 | c
   4 | d
   5 | e
   6 | f
   7 | g
   8 | h
   9 | i
  10 | j
-----+-------
(10 rows)

$ cockroach-sql --insecure --execute="DELETE FROM db1.t1"

Error: pq: rejected: DELETE without WHERE clause (sql_safe_updates = true)
Failed running "sql"

However, to allow an "unsafe" statement, you can set --safe-updates=false:

$ cockroach-sql --insecure --safe-updates=false --execute="DELETE FROM db1.t1"

DELETE 10

Reveal the SQL statements sent implicitly by the command-line utility

In this example, we use the --execute flag to execute statements from the command line and the --echo-sql flag to reveal SQL statements sent implicitly:

$ cockroach-sql --insecure \
--execute="CREATE TABLE t1 (id INT PRIMARY KEY, name STRING)" \
--execute="INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c')" \
--user=maxroach \
--host=12.345.67.89 \
--database=db1
--echo-sql

# Server version: CockroachDB CCL f8f3c9317 (darwin amd64, built 2017/09/13 15:05:35, go1.8) (same version as client)
# Cluster ID: 847a4ba5-c78a-465a-b1a0-59fae3aab520
> SET sql_safe_updates = TRUE
> CREATE TABLE t1 (id INT PRIMARY KEY, name STRING)
CREATE TABLE
> INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c')
INSERT 3

In this example, we start the interactive SQL shell and enable the echo shell option to reveal SQL statements sent implicitly:

$ cockroach-sql --insecure \
--user=maxroach \
--host=12.345.67.89 \
--database=db1

> \set echo

> INSERT INTO db1.t1 VALUES (4, 'd'), (5, 'e'), (6, 'f');

> INSERT INTO db1.t1 VALUES (4, 'd'), (5, 'e'), (6, 'f');
INSERT 3

Time: 2.426534ms

> SHOW TRANSACTION STATUS
> SHOW DATABASE

Repeat a SQL statement

Repeating SQL queries on a table can be useful for monitoring purposes. With the --watch flag, you can repeat the statements specified with a --execute or -e flag periodically, until a SQL error occurs or the process is terminated.

For example, if you want to monitor the number of queries running on the current node, you can use cockroach-sql with the --watch flag to query the node's crdb_internal.node_statement_statistics table for the query count:

$ cockroach-sql --insecure \
--execute="SELECT SUM(count) FROM crdb_internal.node_statement_statistics" \
--watch 1m

  sum
-------
  926
(1 row)
  sum
--------
  4227
(1 row)
^C

In this example, the statement is executed every minute. We let the process run for a couple minutes before terminating it with Ctrl+C.

Connect to a cluster listening for Unix domain socket connections

To connect to a cluster that is running on the same machine as your client and is listening for Unix domain socket connections, specify a Unix domain socket URI with the --url connection parameter.

For example, suppose you start a single-node cluster with the following cockroach start-single-node command:

$ cockroach start-single-node --insecure --socket-dir=/tmp

CockroachDB node starting at  (took 1.3s)
build:               CCL v23.1.29 @ 2024-11-14 00:00:00
webui:               http://Jesses-MBP-2:8080
sql:                 postgresql://root@Jesses-MBP-2:26257?sslmode=disable
RPC client flags:    ./cockroach <client cmd> --host=Jesses-MBP-2:26257 --insecure
socket:              /tmp/.s.PGSQL.26257
logs:                /Users/jesseseldess/Downloads/cockroach-.darwin-10.9-amd64/cockroach-data/logs
temp dir:            /Users/jesseseldess/Downloads/cockroach-.darwin-10.9-amd64/cockroach-data/cockroach-temp805054895
external I/O path:   /Users/jesseseldess/Downloads/cockroach-.darwin-10.9-amd64/cockroach-data/extern
store[0]:            path=/Users/jesseseldess/Downloads/cockroach-.darwin-10.9-amd64/cockroach-data
storage engine:      pebble
status:              initialized new cluster
clusterID:           455ad71d-21d4-424a-87ad-8097b6b5b99f
nodeID:              1

To connect to this cluster with a socket:

$ cockroach-sql --url='postgres://root@?host=/tmp&port=26257'

See also

• Client Connection Parameters
• cockroach demo
• cockroach Commands Overview
• SQL Statements
• Learn CockroachDB SQL

Was this helpful?