cockroach-sql

$ cockroach-sql <flags>

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

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

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

> \q

$ cockroach-sql --help

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

\dn p%

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

\set prompt1 %n@%m/%/

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

> \h UPDATE

Command:     UPDATE
Description: update rows of a table
Category:    data manipulation
Syntax:
UPDATE

[[AS] ] SET ... [WHERE ] [RETURNING ]
See also:
SHOW TABLES
INSERT
UPSERT
DELETE
https://www.cockroachlabs.com/docs/v26.2/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/v26.2/functions-and-operators.html

> 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".

# 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

# 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

> 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)

# 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

# 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

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

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

$ 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

| row | chick | turtle |
| --- | --- | --- |
| 1 | 🐥 | 🐢 |
|  |  |  |
| --- | --- | --- |
| 1 row | | |

$ 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
🐥	🐢

$ 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)

\set border=3
SELECT * FROM animals;

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

$ 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

> \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

$ 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

> \! 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

> 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

$ 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"

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

DELETE 10

$ 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

$ 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

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

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

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

CockroachDB node starting at  (took 1.3s)
build:               CCL  @
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

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