SQL Feature Support in CockroachDB v24.1

On this page Carat arrow pointing down

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
BOOLEAN ✓ Standard BOOL documentation
BYTES ✓ CockroachDB Extension BYTES documentation
COLLATE ✓ Standard COLLATE documentation
DATE ✓ Standard DATE documentation
DECIMAL, NUMERIC ✓ Standard DECIMAL documentation
ENUM ✓ PostgreSQL Extension 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
OID ✓ PostgreSQL Extension OID 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
TSQUERY ✓ Standard TSQUERY documentation
TSVECTOR ✓ Standard TSVECTOR 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. See also SQL Name Resolution.
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
Trigram indexes ✓ PostgreSQL Extension Trigram indexes documentation
Partial indexes ✓ Common Extension Partial indexes documentation
Spatial indexes ✓ Common Extension Spatial indexes documentation
Multiple indexes per query Partial Common Extension Index selection
Full-text indexes ✓ Common Extension Full-text search documentation
Expression indexes ✓ Common Extension Expression indexes
Prefix indexes ✗ Common Extension Implement using Expression indexes
Hash indexes ✗ Common Extension Improves performance of queries looking for single, exact values
Hash-sharded indexes ✓ CockroachDB Extension Hash-sharded Indexes documentation

Schema changes

Component Supported Type Details
ALTER TABLE ✓ Standard ALTER TABLE documentation
Database renames ✓ Standard ALTER DATABASE ... RENAME TO documentation
Table renames ✓ Standard ALTER TABLE ... RENAME TO documentation
Column renames ✓ Standard RENAME COLUMN documentation
Altering a column's data type ✓ Standard 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 ALTER INDEX ... RENAME TO 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 CREATE SCHEMA documentation
Removing user-defined schemas ✓ Standard DROP SCHEMA documentation
Altering user-defined schemas ✓ Standard ALTER 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 ✓ Common Extension PL/pgSQL documentation.
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. For more information, see Limit Query Results.
LIMIT with OFFSET ✓ Common Extension Skip a number of rows, and then limit the size of the return set. For more information, see Limit Query Results.
RETURNING ✓ Common Extension Retrieve a table of rows statements affect. For examples, see the INSERT and DELETE documentation.

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) ✓ 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. Works only with small data sets.
Scalar subqueries Partial Standard Non-correlated subqueries are supported, as are most correlated subqueries. 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 Ownership documentation
Privileges ✓ Standard Privileges documentation
Default privileges ✓ PostgreSQL Extension Default privileges documentation

Miscellaneous

Component Supported Type Details
Column families ✓ CockroachDB Extension Column Families documentation
Computed columns (stored and virtual) ✓ Common Extension Computed Columns documentation
ON UPDATE expressions ✓ MySQL Extension ON UPDATE expressions documentation
Multi-region capabilities ✓ CockroachDB Extension Multi-region documentation
System catalog schemas ✓ Standard, PostgreSQL/CockroachDB Extension crdb_internal (CockroachDB Extension)
information_schema (Standard)
pg_catalog (PostgreSQL Extension)
pg_extension (PostgreSQL Extension)
Sequences ✓ Common Extension CREATE SEQUENCE documentation
Identity columns ✓ Common Extension Identity columns documentation
Views ✓ Standard Views documentation
Materialized views ✓ Common Extension Materialized views documentation
Window functions ✓ Standard Window Functions documentation
Common table expressions Partial Common Extension Common Table Expressions documentation
Stored procedures ✓ Common Extension Stored procedures documentation
Cursors Partial Standard Cursors documentation
Triggers ✗ Standard Execute a set of commands whenever a specified event occurs. GitHub issue tracking trigger support.
Row-level TTL ✓ Common Extension Automatically delete expired rows. For more information, see Batch-delete expired data with Row-Level TTL.
User-defined functions Partial Standard User-Defined Functions documentation
CREATE EXTENSION "uuid-ossp" ✓ Common Extension Provides access to several additional UUID generation functions. Note that these UUID functions are available without typing CREATE EXTENSION "uuid-ossp". CockroachDB does not have full support for CREATE EXTENSION. GitHub issue tracking CREATE EXTENSION support.

Yes No
On this page

Yes No