SQL Feature Support in CockroachDB v19.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

ComponentSupportedTypeDetails
Identifiers✓StandardIdentifiers documentation
INT✓StandardINT documentation
FLOAT, REAL✓StandardFLOAT documentation
BOOLEAN✓StandardBOOL documentation
DECIMAL, NUMERIC✓StandardDECIMAL documentation
NULL✓StandardNULL-handling documentation
BYTES✓CockroachDB ExtensionBYTES documentation
Automatic key generation✓Common ExtensionAutomatic key generation FAQ
STRING, CHARACTER✓StandardSTRING documentation
COLLATE✓StandardCOLLATE documentation
AUTO INCREMENTAlternativeCommon ExtensionAutomatic key generation FAQ
Key-value pairsAlternativeExtensionKey-Value FAQ
ARRAY✓StandardARRAY documentation
UUID✓PostgreSQL ExtensionUUID documentation
JSON✓Common ExtensionJSONB documentation
TIME✓StandardTIME documentation
XML✗StandardXML data can be stored as BYTES, but we do not offer XML parsing.
UNSIGNED INT✗Common ExtensionUNSIGNED INT causes numerous casting issues, so we do not plan to support it.
SET, ENUM✗MySQL, PostgreSQL ExtensionOnly allow rows to contain values from a defined set of terms.
INET✓PostgreSQL ExtensionINET documentation

Constraints

ComponentSupportedTypeDetails
Not Null✓StandardNot Null documentation
Unique✓StandardUnique documentation
Primary Key✓StandardPrimary Key documentation
Check✓StandardCheck documentation
Foreign Key✓StandardForeign Key documentation
Default Value✓StandardDefault Value documentation

Transactions

ComponentSupportedTypeDetails
Transactions (ACID semantics)✓StandardTransactions documentation
BEGIN✓StandardBEGIN documentation
COMMIT✓StandardCOMMIT documentation
ROLLBACK✓StandardROLLBACK documentation
SAVEPOINT✓CockroachDB ExtensionWhile SAVEPOINT is part of the SQL standard, we only support our extension of it

Indexes

ComponentSupportedTypeDetails
Indexes✓Common ExtensionIndexes documentation
Multi-column indexes✓Common ExtensionWe do not limit on the number of columns indexes can include
Covering indexes✓Common ExtensionStoring Columns documentation
Inverted indexes✓Common ExtensionInverted Indexes documentation
Multiple indexes per queryPlannedCommon ExtensionUse multiple indexes to filter the table's values for a single query
Full-text indexesPlannedCommon ExtensionGitHub issue tracking full-text index support
Prefix/Expression IndexesPotentialCommon ExtensionApply expressions (such as LOWER()) to values before indexing them
Geospatial indexesPotentialCommon ExtensionImproves performance of queries calculating geospatial data
Hash indexes✗Common ExtensionImproves performance of queries looking for single, exact values
Partial indexes✗Common ExtensionOnly index specific rows from indexed columns

Schema changes

ComponentSupportedTypeDetails
ALTER TABLE✓StandardALTER TABLE documentation
Database renames✓StandardRENAME DATABASE documentation
Table renames✓StandardRENAME TABLE documentation
Column renames✓StandardRENAME COLUMN documentation
Adding columns✓StandardADD COLUMN documentation
Removing columns✓StandardDROP COLUMN documentation
Adding constraints✓StandardADD CONSTRAINT documentation
Removing constraints✓StandardDROP CONSTRAINT documentation
Index renames✓StandardRENAME INDEX documentation
Adding indexes✓StandardCREATE INDEX documentation
Removing indexes✓StandardDROP INDEX documentation

Statements

ComponentSupportedTypeDetails
Common statements✓StandardSQL Statements documentation
UPSERT✓PostgreSQL, MSSQL ExtensionUPSERT documentation
EXPLAIN✓Common ExtensionEXPLAIN documentation
SELECT INTOAlternativeCommon ExtensionYou can replicate similar functionality using CREATE TABLE and then INSERT INTO … SELECT ….

Clauses

ComponentSupportedTypeDetails
Common clauses✓StandardSQL Grammar documentation
LIMIT✓Common ExtensionLimit the number of rows a statement returns.
LIMIT with OFFSET✓Common ExtensionSkip a number of rows, and then limit the size of the return set.
RETURNING✓Common ExtensionRetrieve a table of rows statements affect.

Table expressions

ComponentSupportedTypeDetails
Table and View references✓StandardTable expressions documentation
AS in table expressions✓StandardAliased table expressions documentation
JOIN (INNER, LEFT, RIGHT, FULL, CROSS)FunctionalStandardJoin expressions documentation
Sub-queries as table expressionsPartialStandardNon-correlated subqueries are supported, as are most correlated subqueries.
Table generator functionsPartialPostgreSQL ExtensionTable generator functions documentation
WITH ORDINALITY✓CockroachDB ExtensionOrdinality annotation documentation

Scalar expressions and boolean formulas

ComponentSupportedTypeDetails
Common functions✓StandardFunctions calls and SQL special forms documentation
Common operators✓StandardOperators documentation
IF/CASE/NULLIF✓StandardConditional expressions documentation
COALESCE/IFNULL✓StandardConditional expressions documentation
AND/OR✓StandardConditional expressions documentation
LIKE/ILIKE✓StandardString pattern matching documentation
SIMILAR TO✓StandardSQL regexp pattern matching documentation
Matching using POSIX regular expressions✓Common ExtensionPOSIX regexp pattern matching documentation
EXISTSPartialStandardNon-correlated subqueries are supported, as are most correlated subqueries. Currently works only with small data sets.
Scalar subqueriesPartialStandardNon-correlated subqueries are supported, as are most correlated subqueries. Currently works only with small data sets.
Bitwise arithmetic✓Common ExtensionOperators documentation
Array constructors and subscriptingPartialPostgreSQL ExtensionArray expression documentation: Constructor syntax and Subscripting
COLLATE✓StandardCollation expressions documentation
Column ordinal references✓CockroachDB ExtensionColumn references documentation
Type annotations✓CockroachDB ExtensionType annotations documentation

Permissions

ComponentSupportedTypeDetails
Users✓StandardGRANT documentation
Privileges✓StandardPrivileges documentation

Miscellaneous

ComponentSupportedTypeDetails
Column families✓CockroachDB ExtensionColumn Families documentation
Interleaved tables✓CockroachDB ExtensionInterleaved Tables documentation
Parallel Statement Execution✓CockroachDB ExtensionParallel Statement Execution documentation
Information Schema✓StandardInformation Schema documentation
Views✓StandardViews documentation
Window functions✓StandardWindow Functions documentation
Common Table ExpressionsPartialCommon ExtensionCommon Table Expressions documentation
Stored ProceduresPlannedCommon ExtensionExecute a procedure explicitly.
Cursors✗StandardTraverse a table's rows.
Triggers✗StandardExecute a set of commands whenever a specified event occurs.
Sequences✓Common ExtensionCREATE SEQUENCE documentation

Was this page helpful?
YesNo