On this page 
Making CockroachDB easy to use is a top priority for us, so we chose to implement SQL. However, even though SQL has a standard, no database implements all of it, nor do any of them have standard implementations of all features.
To understand which standard SQL features we support (as well as common extensions to the standard), use the table below.
- Component lists the components that are commonly considered part of SQL.
- Supported shows CockroachDB's level of support for the component.
- Type indicates whether the component is part of the SQL Standard or is an Extension created by ourselves or others.
- Details provides greater context about the component.
Features
Row values
| Component | Supported | Type | Details |
|---|---|---|---|
ARRAY |
✓ | Standard | ARRAY documentation |
AUTO INCREMENT(Automatic key generation) |
Alternative | Common Extension | Automatic key generation FAQ |
BIT |
✓ | Standard | BIT documentation |
BYTES |
✓ | CockroachDB Extension | BYTES documentation |
BOOLEAN |
✓ | Standard | BOOL documentation |
COLLATE |
✓ | Standard | COLLATE documentation |
DATE |
✓ | Standard | DATE documentation |
DECIMAL, NUMERIC |
✓ | Standard | DECIMAL documentation |
ENUM |
✓ | PostgreSQL Extension | New in v20.2: ENUM documentation |
FLOAT, REAL |
✓ | Standard | FLOAT documentation |
INET |
✓ | PostgreSQL Extension | INET documentation |
INT |
✓ | Standard | INT documentation |
INTERVAL |
✓ | Standard | INTERVAL documentation |
JSON/JSONB |
✓ | Common Extension | JSONB documentation |
NULL |
✓ | Standard | NULL-handling documentation |
SERIAL |
✓ | PostgreSQL Extension | SERIAL documentation |
SET |
✗ | MySQL | Only allow rows to contain values from a defined set of terms. |
STRING, CHARACTER |
✓ | Standard | STRING documentation |
TIME |
✓ | Standard | TIME documentation |
TIMESTAMP/TIMESTAMPTZ |
✓ | Standard | TIMESTAMP documentation |
UNSIGNED INT |
✗ | Common Extension | UNSIGNED INT causes numerous casting issues, so we do not plan to support it. |
UUID |
✓ | PostgreSQL Extension | UUID documentation |
| Identifiers | ✓ | Standard | Identifiers documentation |
| Key-value pairs | Alternative | Extension | Key-Value FAQ |
| XML | ✗ | Standard | XML data can be stored as BYTES, but we do not offer XML parsing. |
Constraints
| Component | Supported | Type | Details |
|---|---|---|---|
| Not Null | ✓ | Standard | Not Null documentation |
| Unique | ✓ | Standard | Unique documentation |
| Primary Key | ✓ | Standard | Primary Key documentation |
| Check | ✓ | Standard | Check documentation |
| Foreign Key | ✓ | Standard | Foreign Key documentation |
| Default Value | ✓ | Standard | Default Value documentation |
Transactions
| Component | Supported | Type | Details |
|---|---|---|---|
| Transactions (ACID semantics) | ✓ | Standard | Transactions documentation |
BEGIN |
✓ | Standard | BEGIN documentation |
COMMIT |
✓ | Standard | COMMIT documentation |
ROLLBACK |
✓ | Standard | ROLLBACK documentation |
SAVEPOINT |
✓ | Standard with CockroachDB extensions | CockroachDB supports nested transactions using SAVEPOINT |
Indexes
| Component | Supported | Type | Details |
|---|---|---|---|
| Indexes | ✓ | Common Extension | Indexes documentation |
| Multi-column indexes | ✓ | Common Extension | We do not limit on the number of columns indexes can include |
| Covering indexes | ✓ | Common Extension | Storing Columns documentation |
| GIN indexes | ✓ | Common Extension | GIN Indexes documentation |
| Partial indexes | ✓ | Common Extension | New in v20.2: Partial indexes documentation |
| Spatial indexes | ✓ | Common Extension | New in v20.2: Spatial indexes documentation |
| Multiple indexes per query | Partial | Common Extension | Use multiple indexes for a single query |
| Full-text indexes | Planned | Common Extension | GitHub issue tracking full-text index support |
| Prefix/Expression Indexes | Potential | Common Extension | Apply expressions (such as LOWER()) to values before indexing them |
| Hash indexes | ✗ | Common Extension | Improves performance of queries looking for single, exact values |
Schema changes
| Component | Supported | Type | Details |
|---|---|---|---|
ALTER TABLE |
✓ | Standard | ALTER TABLE documentation |
| Database renames | ✓ | Standard | RENAME DATABASE documentation |
| Table renames | ✓ | Standard | RENAME TABLE documentation |
| Column renames | ✓ | Standard | RENAME COLUMN documentation |
| Altering a column's data type | ✓ | Standard | New in v20.2: ALTER COLUMN documentation |
| Adding columns | ✓ | Standard | ADD COLUMN documentation |
| Removing columns | ✓ | Standard | DROP COLUMN documentation |
| Adding constraints | ✓ | Standard | ADD CONSTRAINT documentation |
| Removing constraints | ✓ | Standard | DROP CONSTRAINT documentation |
| Index renames | ✓ | Standard | RENAME INDEX documentation |
| Adding indexes | ✓ | Standard | CREATE INDEX documentation |
| Removing indexes | ✓ | Standard | DROP INDEX documentation |
| Altering a primary key | ✓ | Standard | ALTER PRIMARY KEY documentation |
| Adding user-defined schemas | ✓ | Standard | New in v20.2: CREATE SCHEMA documentation |
| Removing user-defined schemas | ✓ | Standard | New in v20.2: DROP SCHEMA documentation |
| Altering user-defined schemas | ✓ | Standard | New in v20.2: ALTER SCHEMA documentation |
| Converting a database to a user-defined schema | ✓ | CockroachDB Extension | New in v20.2: CONVERT TO SCHEMA documentation |
Statements
| Component | Supported | Type | Details |
|---|---|---|---|
| Common statements | ✓ | Standard, PostgreSQL/CockroachDB Extension | SQL Statements documentation |
UPSERT |
✓ | PostgreSQL, MSSQL Extension | UPSERT documentation |
EXPLAIN |
✓ | Common Extension | EXPLAIN documentation |
SELECT INTO |
Alternative | Common Extension | You can replicate similar functionality using CREATE TABLE and then INSERT INTO ... SELECT .... |
SELECT FOR UPDATE |
✓ | Common Extension | SELECT FOR UPDATE documentation |
Clauses
| Component | Supported | Type | Details |
|---|---|---|---|
| Common clauses | ✓ | Standard | SQL Grammar documentation |
LIMIT |
✓ | Common Extension | Limit the number of rows a statement returns. |
LIMIT with OFFSET |
✓ | Common Extension | Skip a number of rows, and then limit the size of the return set. |
RETURNING |
✓ | Common Extension | Retrieve a table of rows statements affect. |
Table expressions
| Component | Supported | Type | Details |
|---|---|---|---|
| Table and View references | ✓ | Standard | Table expressions documentation |
AS in table expressions |
✓ | Standard | Aliased table expressions documentation |
JOIN (INNER, LEFT, RIGHT, FULL, CROSS) |
Functional | Standard | JOIN expressions documentation |
| Sub-queries as table expressions | Partial | Standard | Non-correlated subqueries are supported, as are most correlated subqueries. |
| Table generator functions | Partial | PostgreSQL Extension | Table generator functions documentation |
WITH ORDINALITY |
✓ | CockroachDB Extension | Ordinality annotation documentation |
Scalar expressions and boolean formulas
| Component | Supported | Type | Details |
|---|---|---|---|
| Common functions | ✓ | Standard | Functions calls and SQL special forms documentation |
| Common operators | ✓ | Standard | Operators documentation |
IF/CASE/NULLIF |
✓ | Standard | Conditional expressions documentation |
COALESCE/IFNULL |
✓ | Standard | Conditional expressions documentation |
AND/OR |
✓ | Standard | Conditional expressions documentation |
LIKE/ILIKE |
✓ | Standard | String pattern matching documentation |
SIMILAR TO |
✓ | Standard | SQL regexp pattern matching documentation |
| Matching using POSIX regular expressions | ✓ | Common Extension | POSIX regexp pattern matching documentation |
EXISTS |
Partial | Standard | Non-correlated subqueries are supported, as are most correlated subqueries. Currently works only with small data sets. |
| Scalar subqueries | Partial | Standard | Non-correlated subqueries are supported, as are most correlated subqueries. Currently works only with small data sets. |
| Bitwise arithmetic | ✓ | Common Extension | Operators documentation |
| Array constructors and subscripting | Partial | PostgreSQL Extension | Array expression documentation: Constructor syntax and Subscripting |
COLLATE |
✓ | Standard | Collation expressions documentation |
| Column ordinal references | ✓ | CockroachDB Extension | Column references documentation |
| Type annotations | ✓ | CockroachDB Extension | Type annotations documentation |
Permissions
| Component | Supported | Type | Details |
|---|---|---|---|
| Users | ✓ | Standard | Users documentation |
| Roles | ✓ | Standard | Roles documentation |
| Object ownership | ✓ | Common Extension | New in v20.2: Ownership documentation |
| Privileges | ✓ | Standard | Privileges documentation |
Miscellaneous
| Component | Supported | Type | Details |
|---|---|---|---|
| Column families | ✓ | CockroachDB Extension | Column Families documentation |
| Interleaved tables | ✓ | CockroachDB Extension | Interleaved Tables documentation Warning: Interleaving is deprecated in CockroachDB v20.2. For details, see INTERLEAVE IN PARENT Deprecation. |
| Information Schema | ✓ | Standard | Information Schema documentation |
| Views | ✓ | Standard | Views documentation |
| Materialized views | ✓ | Common Extension | New in v20.2: Materialized views documentation |
| Window functions | ✓ | Standard | Window Functions documentation |
| Common Table Expressions | Partial | Common Extension | Common Table Expressions documentation |
| Stored Procedures | Planned | Common Extension | Execute a procedure explicitly. |
| Cursors | ✗ | Standard | Traverse a table's rows. |
| Triggers | ✗ | Standard | Execute a set of commands whenever a specified event occurs. |
| Sequences | ✓ | Common Extension | CREATE SEQUENCE documentation |
