SQL Feature Support in CockroachDB v24.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
`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
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
`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
Users	✓	Standard
Roles	✓	Standard
Object ownership	✓	Common Extension
Privileges	✓	Standard
Default privileges	✓	PostgreSQL Extension

PostgreSQL extensions

CockroachDB accepts some CREATE EXTENSION statements for compatibility with PostgreSQL tools and migrations. In CockroachDB, these statements are no-ops: if the related functionality is supported, it’s available without running CREATE EXTENSION. Further, when CREATE EXTENSION syntax is accepted by CockroachDB, that does not imply full compatibility with the corresponding PostgreSQL extension.

Component	Support level	Type	Details
`CREATE EXTENSION "postgis"`	Partial	PostgreSQL Extension	CockroachDB supports , , and many . Not all PostGIS functions are supported.
`CREATE EXTENSION "pg_trgm"`	Partial	PostgreSQL Extension	CockroachDB supports and trigram functions and operators for fuzzy string matching and pattern matching. Some PostgreSQL `pg_trgm` syntax and features are not supported.
`CREATE EXTENSION "fuzzystrmatch"`	✓	Common Extension	Provides PostgreSQL-compatible fuzzy string matching functions including `soundex()`, `difference()`, `levenshtein()`, and `metaphone()`.
`CREATE EXTENSION "pgcrypto"`	Partial	PostgreSQL Extension	CockroachDB supports some PostgreSQL-compatible , including `crypt()`, `digest()`, `gen_random_uuid()`, `gen_salt()`, and `hmac()`. CockroachDB also supports such as `encrypt()`, `encrypt_iv()`, `decrypt()`, and `decrypt_iv()`. CockroachDB does not support every PostgreSQL `pgcrypto` function.
`CREATE EXTENSION "uuid-ossp"`	✓	Common Extension	Provides access to several additional .
`CREATE EXTENSION "btree_gin"` `CREATE EXTENSION "btree_gist"`	✗	PostgreSQL Extension	CockroachDB does not support PostgreSQL `btree_gin` or `btree_gist` operator class extensions. Depending on the use case, use CockroachDB , , , or .
`CREATE EXTENSION "postgres_fdw"`	✗	PostgreSQL Extension	CockroachDB does not support PostgreSQL foreign data wrappers. To reference objects in another database in the same CockroachDB cluster, use , subject to the limitations for cross-database object references.
`CREATE EXTENSION "citext"`	✗	PostgreSQL Extension	CockroachDB does not support the PostgreSQL `citext` extension. For case-insensitive matching, use SQL expressions such as `lower()` or case-insensitive pattern matching with `ILIKE`, depending on the query.
`CREATE EXTENSION "pg_stat_statements"`	✗	PostgreSQL Extension	CockroachDB does not support the PostgreSQL `pg_stat_statements` extension. Use CockroachDB and SQL observability features instead.

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	✗	Standard	Execute a set of commands whenever a specified event occurs.
Row-level TTL	✓	Common Extension	Automatically delete expired rows. For more information, see .
User-defined functions	Partial	Standard

Get Started

CockroachDB and AI

Feature Overview

Data Resilience

Connect to an Application

Self-Hosted Deployments

Schema Design

Reads and Writes

Stream Data

Cross-Cluster Replication

Multi-Region Capabilities

Optimize Performance

Integrate

SQL Feature Support in CockroachDB v24.1

Features

Row values

Constraints

Transactions

Indexes

Schema changes

Statements

Clauses

Table expressions

Scalar expressions and Boolean formulas

Permissions

PostgreSQL extensions

Miscellaneous

​Features

​Row values

​Constraints

​Transactions

​Indexes

​Schema changes

​Statements

​Clauses

​Table expressions

​Scalar expressions and Boolean formulas

​Permissions

​PostgreSQL extensions

​Miscellaneous

Features

Row values

Constraints

Transactions

Indexes

Schema changes

Statements

Clauses

Table expressions

Scalar expressions and Boolean formulas

Permissions

PostgreSQL extensions

Miscellaneous