SQL Statements

CockroachDB supports a comprehensive set of SQL statements for defining schemas, manipulating data, controlling access, and managing transactions.

In the cockroach sql shell, use \h [statement] to get inline help about any statement.

Data Definition Statements

Data Definition Language (DDL) statements define and modify database schema objects.

Tables

CREATE TABLE

ddl

Create a new table in a database.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  username STRING(50) NOT NULL,
  email STRING UNIQUE,
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE

ddl

Apply schema changes to a table.

ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP COLUMN bio;

DROP TABLE

ddl

Remove a table and all its data.

DROP TABLE users;
DROP TABLE IF EXISTS old_data;

TRUNCATE

ddl

Delete all rows from specified tables.

TRUNCATE TABLE logs;
TRUNCATE TABLE sessions, temp_data;

Indexes

CREATE INDEX

ddl

Create an index on a table to improve query performance.

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created ON orders(created_at DESC);

ALTER INDEX

ddl

Modify an existing index.

ALTER INDEX idx_email RENAME TO idx_user_email;

DROP INDEX

ddl

Remove an index from a table.

DROP INDEX idx_user_email;

Databases and Schemas

CREATE DATABASE

ddl

Create a new database.

CREATE DATABASE myapp;
CREATE DATABASE IF NOT EXISTS staging;

ALTER DATABASE

ddl

Modify database configuration.

ALTER DATABASE myapp CONFIGURE ZONE USING num_replicas = 3;

DROP DATABASE

ddl

Remove a database and all its objects.

DROP DATABASE myapp CASCADE;

CREATE SCHEMA

ddl

Create a user-defined schema.

CREATE SCHEMA analytics;

Views

CREATE VIEW

ddl

Create a view as a stored query.

CREATE VIEW active_users AS
  SELECT * FROM users WHERE last_login > now() - INTERVAL '30 days';

CREATE MATERIALIZED VIEW

ddl

Create a materialized view with stored results.

CREATE MATERIALIZED VIEW daily_stats AS
  SELECT date_trunc('day', created_at) AS day, count(*)
  FROM orders
  GROUP BY day;

REFRESH

ddl

Refresh the stored query results of a materialized view.

REFRESH MATERIALIZED VIEW daily_stats;

Data Manipulation Statements

Data Manipulation Language (DML) statements query and modify data in tables.

SELECT
INSERT
UPDATE
DELETE
UPSERT

Query data from tables.

SELECT * FROM users WHERE email LIKE '%@example.com';

SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.username
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 10;

Insert new rows into a table.

INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');

INSERT INTO orders (user_id, total, status)
VALUES
  (1, 99.99, 'pending'),
  (2, 149.99, 'shipped');

Modify existing rows in a table.

UPDATE users
SET last_login = now()
WHERE id = '7f9c24e8-3b12-4fef-91e0-56a2d5a246ec';

UPDATE orders
SET status = 'cancelled'
WHERE created_at < now() - INTERVAL '90 days'
  AND status = 'pending';

Remove rows from a table.

DELETE FROM sessions WHERE expires_at < now();

DELETE FROM users WHERE id = '123e4567-e89b-12d3-a456-426614174000';

Insert rows or update them if they already exist.

UPSERT INTO user_preferences (user_id, theme, language)
VALUES ('user123', 'dark', 'en');

Advanced SELECT

SELECT FOR UPDATE

dml

Lock rows for update to control concurrent access.

BEGIN;
SELECT * FROM inventory WHERE sku = '8675309' FOR UPDATE;
UPDATE inventory SET quantity = quantity - 1 WHERE sku = '8675309';
COMMIT;

WITH (CTE)

dml

Use Common Table Expressions for complex queries.

WITH regional_sales AS (
  SELECT region, SUM(amount) AS total_sales
  FROM orders
  GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 1000000;

Data Control Statements

Data Control Language (DCL) statements manage access permissions and security.

CREATE USER alice WITH PASSWORD 'secure123';
CREATE ROLE developers;

GRANT developers TO alice;

ALTER USER alice WITH PASSWORD 'newsecure456';

DROP USER IF EXISTS olduser;

Transaction Control Statements

Manage transaction boundaries and behavior.

BEGIN/COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION

Start and commit transactions.

BEGIN;

INSERT INTO accounts (id, balance) VALUES (1, 1000);
INSERT INTO accounts (id, balance) VALUES (2, 500);

COMMIT;

Abort a transaction and discard changes.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Something went wrong
ROLLBACK;

Create nested transactions with savepoints.

BEGIN;

SAVEPOINT sp1;
UPDATE users SET status = 'active' WHERE id = 1;

SAVEPOINT sp2;
DELETE FROM logs WHERE created_at < now() - INTERVAL '1 year';

-- Rollback only the DELETE
ROLLBACK TO SAVEPOINT sp2;

-- Release the first savepoint
RELEASE SAVEPOINT sp1;

COMMIT;

Configure transaction properties.

BEGIN PRIORITY HIGH;

SET TRANSACTION PRIORITY LOW;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION READ ONLY;

Query Planning Statements

Analyze and optimize query execution.

EXPLAIN

query

Show the execution plan for a query.

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

EXPLAIN ANALYZE

query

Execute the query and show actual execution statistics.

EXPLAIN ANALYZE SELECT u.username, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.username;

CREATE STATISTICS

query

Generate statistics for the cost-based optimizer.

CREATE STATISTICS stats_users FROM users;

Show Statements

Inspect database metadata and configuration.

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM users;
SHOW INDEXES FROM orders;
SHOW CONSTRAINTS FROM users;

Job Management Statements

Manage long-running operations like backups and schema changes.

SHOW JOBS

admin

View information about jobs.

SHOW JOBS;
SHOW JOBS WHEN COMPLETE (SELECT id FROM [SHOW JOBS] WHERE job_type = 'BACKUP');

PAUSE/RESUME JOB

admin

Pause or resume a running job.

PAUSE JOB 123456789;
RESUME JOB 123456789;

CANCEL JOB

admin

Cancel a running job.

CANCEL JOB 123456789;

Get Started

Architecture

Core Concepts

Deployment

SQL Reference

Operations

Migration

Developer Guide

Data Definition Statements

Tables

Indexes

Databases and Schemas

Views

Data Manipulation Statements

Advanced SELECT

Data Control Statements

Transaction Control Statements

Query Planning Statements

Show Statements

Job Management Statements

See Also

Build docs developers (and LLMs) love

Get Started

Architecture

Core Concepts

Deployment

SQL Reference

Operations

Migration

Developer Guide

​Data Definition Statements

​Tables

​Indexes

​Databases and Schemas

​Views

​Data Manipulation Statements

​Advanced SELECT

​Data Control Statements

​Transaction Control Statements

​Query Planning Statements

​Show Statements

​Job Management Statements

​See Also

Build docs developers (and LLMs) love

Data Definition Statements

Tables

Indexes

Databases and Schemas

Views

Data Manipulation Statements

Advanced SELECT

Data Control Statements

Transaction Control Statements

Query Planning Statements

Show Statements

Job Management Statements

See Also