SQL Feature Support in CockroachDB v20.2

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