# SQL Feature Support in CockroachDB v20.1

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
--- | --- | --- | ---
Identifiers | ✓ | Standard | Identifiers documentation
`INT` | ✓ | Standard | `INT` documentation
`FLOAT`, `REAL` | ✓ | Standard | `FLOAT` documentation
`BOOLEAN` | ✓ | Standard | `BOOL` documentation
`DECIMAL`, `NUMERIC` | ✓ | Standard | `DECIMAL` documentation
`NULL` | ✓ | Standard | NULL-handling documentation
`BYTES` | ✓ | CockroachDB Extension | `BYTES` documentation
Automatic key generation | ✓ | Common Extension | Automatic key generation FAQ
`STRING`, `CHARACTER` | ✓ | Standard | `STRING` documentation
`COLLATE` | ✓ | Standard | `COLLATE` documentation
`AUTO INCREMENT` | Alternative | Common Extension | Automatic key generation FAQ
Key-value pairs | Alternative | Extension | Key-Value FAQ
`ARRAY` | ✓ | Standard | `ARRAY` documentation
`UUID` | ✓ | PostgreSQL Extension | `UUID` documentation
JSON | ✓ | Common Extension | `JSONB` documentation
`TIME` | ✓ | Standard | `TIME` documentation
XML | ✗ | Standard | XML data can be stored as `BYTES`, but we do not offer XML parsing.
`UNSIGNED INT` | ✗ | Common Extension | `UNSIGNED INT` causes numerous casting issues, so we do not plan to support it.
`SET`, `ENUM` | ✗ | MySQL, PostgreSQL Extension | Only allow rows to contain values from a defined set of terms.
`INET` | ✓ | PostgreSQL Extension | `INET` documentation

### 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 | New in v20.1: 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
Inverted indexes | ✓ | Common Extension | Inverted Indexes documentation
Multiple indexes per query | Planned | Common Extension | Use multiple indexes to filter the table's values 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
Geospatial indexes | Potential | Common Extension | Improves performance of queries calculating geospatial data
Hash indexes | ✗ | Common Extension | Improves performance of queries looking for single, exact values
Partial indexes | ✗ | Common Extension | Only index specific rows from indexed columns

### 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
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 | New in v20.1: `ALTER PRIMARY KEY` documentation

### Statements

Component | Supported | Type | Details
--- | --- | --- | ---
Common statements | ✓ | Standard | 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 | New in v20.1: `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 | `GRANT` documentation
Privileges | ✓ | Standard | Privileges documentation

### Miscellaneous

Component | Supported | Type | Details
--- | --- | --- | ---
Column families | ✓ | CockroachDB Extension | Column Families documentation
Interleaved tables | ✓ | CockroachDB Extension | Interleaved Tables documentation
Information Schema | ✓ | Standard | Information Schema documentation
User-defined Schemas | Planned | Standard | Create, drop, and modify user-defined schemas.
Views | ✓ | Standard | 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