SQL Feature Support in CockroachDB

CockroachDB supports the following standard SQL features and common extensions.

• 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 Cockroach Labs or others.
• Details provides greater context about the component.

Features

Row values

Component	Supported	Type	Details
ARRAY	✓	Standard
AUTO INCREMENT (Automatic key generation)	Alternative	Common Extension
BIT	✓	Standard
BOOLEAN	✓	Standard
BYTES	✓	CockroachDB Extension
COLLATE	✓	Standard
DATE	✓	Standard
DECIMAL, NUMERIC	✓	Standard
ENUM	✓	PostgreSQL Extension
FLOAT, REAL	✓	Standard
INET	✓	PostgreSQL Extension
INT	✓	Standard
INTERVAL	✓	Standard
JSON/JSONB	✓	Common Extension
NULL	✓	Standard
OID	✓	PostgreSQL Extension
SERIAL	✓	PostgreSQL Extension
SET	✗	MySQL	Only allow rows to contain values from a defined set of terms.
STRING, CHARACTER	✓	Standard
TIME	✓	Standard
TIMESTAMP/TIMESTAMPTZ	✓	Standard
TSQUERY	✓	Standard
TSVECTOR	✓	Standard
UNSIGNED INT	✗	Common Extension	UNSIGNED INT causes numerous casting issues, so we do not plan to support it.
UUID	✓	PostgreSQL Extension
Identifiers	✓	Standard	. See also .
Key-value pairs	Alternative	Extension
XML	✗	Standard	XML data can be stored as BYTES, but we do not offer XML parsing.

Constraints

Component	Supported	Type	Details
Not Null	✓	Standard
Unique	✓	Standard
Primary Key	✓	Standard
Check	✓	Standard
Foreign Key	✓	Standard
Default Value	✓	Standard

Transactions

Component	Supported	Type	Details
Transactions (ACID semantics)	✓	Standard
BEGIN	✓	Standard
COMMIT	✓	Standard
ROLLBACK	✓	Standard
SAVEPOINT	✓	Standard with CockroachDB extensions	CockroachDB supports nested transactions using

Indexes

Component	Supported	Type	Details
Indexes	✓	Common Extension
Multi-column indexes	✓	Common Extension	We do not limit on the number of columns indexes can include
Covering indexes	✓	Common Extension
GIN indexes	✓	Common Extension
Trigram indexes	✓	PostgreSQL Extension
Partial indexes	✓	Common Extension
Spatial indexes	✓	Common Extension
Multiple indexes per query	Partial	Common Extension
Full-text indexes	✓	Common Extension
Expression indexes	✓	Common Extension
Prefix indexes	✗	Common Extension	Implement using
Hash indexes	✗	Common Extension	Improves performance of queries looking for single, exact values
Hash-sharded indexes	✓	CockroachDB Extension

Schema changes

Component	Supported	Type	Details
ALTER TABLE	✓	Standard
Database renames	✓	Standard
Table renames	✓	Standard
Column renames	✓	Standard
Altering a column’s data type	✓	Standard
Adding columns	✓	Standard
Removing columns	✓	Standard
Adding constraints	✓	Standard
Removing constraints	✓	Standard
Index renames	✓	Standard
Adding indexes	✓	Standard
Removing indexes	✓	Standard
Altering a primary key	✓	Standard
Adding user-defined schemas	✓	Standard
Removing user-defined schemas	✓	Standard
Altering user-defined schemas	✓	Standard

Statements

Component	Supported	Type	Details
Common statements	✓	Standard, PostgreSQL/CockroachDB Extension
UPSERT	✓	PostgreSQL, MSSQL Extension
EXPLAIN	✓	Common Extension
SELECT ... INTO	✓	Common Extension	.
SELECT FOR UPDATE	✓	Common Extension

Clauses

Component	Supported	Type	Details
Common clauses	✓	Standard
LIMIT	✓	Common Extension	Limit the number of rows a statement returns. For more information, see .
LIMIT with OFFSET	✓	Common Extension	Skip a number of rows, and then limit the size of the return set. For more information, see .
RETURNING	✓	Common Extension	Retrieve a table of rows statements affect. For examples, see the and documentation.

Table expressions

Component	Supported	Type	Details
Table and View references	✓	Standard
AS in table expressions	✓	Standard
JOIN (INNER, LEFT, RIGHT, FULL, CROSS)	✓	Standard
Sub-queries as table expressions	Partial	Standard	Non-correlated subqueries are , as are most .
Table generator functions	Partial	PostgreSQL Extension
WITH ORDINALITY	✓	CockroachDB Extension

Scalar expressions and Boolean formulas

Component	Supported	Type	Details
Common functions	✓	Standard
Common operators	✓	Standard
IF/CASE/NULLIF	✓	Standard
COALESCE/IFNULL	✓	Standard
AND/OR	✓	Standard
LIKE/ILIKE	✓	Standard
SIMILAR TO	✓	Standard
Matching using POSIX regular expressions	✓	Common Extension
EXISTS	Partial	Standard	Non-correlated subqueries are , as are most . Works only with small data sets.
Scalar subqueries	Partial	Standard	Non-correlated subqueries are , as are most . Works only with small data sets.
Bitwise arithmetic	✓	Common Extension
Array constructors and subscripting	Partial	PostgreSQL Extension	Array expression documentation: and
COLLATE	✓	Standard
Column ordinal references	✓	CockroachDB Extension
Type annotations	✓	CockroachDB Extension

Permissions

Component	Supported	Type	Details
Users	✓	Standard
Roles	✓	Standard
Object ownership	✓	Common Extension
Privileges	✓	Standard
Default privileges	✓	PostgreSQL Extension

Miscellaneous

Component	Supported	Type	Details
Column families	✓	CockroachDB Extension
Computed columns (stored and virtual)	✓	Common Extension
ON UPDATE expressions	✓	MySQL Extension
Multi-region capabilities	✓	CockroachDB Extension
System catalog schemas	✓	Standard, PostgreSQL/CockroachDB Extension	(CockroachDB Extension) (Standard) (PostgreSQL Extension) (PostgreSQL Extension)
Sequences	✓	Common Extension
Identity columns	✓	Common Extension
Views	✓	Standard
Materialized views	✓	Common Extension
Window functions	✓	Standard
Common table expressions	Partial	Common Extension
Stored procedures	Partial	Common Extension
Cursors	Partial	Standard
Triggers	Partial	Standard
Row-level TTL	✓	Common Extension	Automatically delete expired rows. For more information, see .
User-defined functions	Partial	Standard
CREATE EXTENSION "fuzzystrmatch"	✓	Common Extension	Provides PostgreSQL-compatible fuzzy string matching functions including soundex(), difference(), levenshtein(), metaphone(), , , and . These functions are available without running CREATE EXTENSION "fuzzystrmatch". CockroachDB does not have full support for CREATE EXTENSION.
CREATE EXTENSION "uuid-ossp"	✓	Common Extension	Provides access to several additional . Note that these UUID functions are available without typing CREATE EXTENSION "uuid-ossp". CockroachDB does not have full support for CREATE EXTENSION.