> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # SQL Feature Support in CockroachDB export const InternalLink = ({version, path = "", children, ...props}) => { let detectedVersion = version || "stable"; if (typeof window !== 'undefined' && !version) { const match = window.location.pathname.match(/\/docs\/([^/]+)/); if (match) { detectedVersion = match[1]; } } const normalizedPath = path.startsWith("/") ? path.slice(1) : path; return {children} ; }; 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 | `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 | | `DOMAIN` | ✓ | Standard | `DOMAIN` 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 | ### 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 spatial data, spatial indexes, and many PostGIS-compatible spatial functions. Not all PostGIS functions are supported. | | `CREATE EXTENSION "pg_trgm"` | Partial | PostgreSQL Extension | CockroachDB supports trigram indexes 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()`, `levenshtein_less_equal()`, `metaphone()`, `dmetaphone()`, `dmetaphone_alt()`, and `daitch_mokotoff()`. | | `CREATE EXTENSION "pgcrypto"` | Partial | PostgreSQL Extension | CockroachDB supports some PostgreSQL-compatible cryptographic functions, including `crypt()`, `digest()`, `gen_random_uuid()`, `gen_salt()`, and `hmac()`. CockroachDB also supports column-level encryption functions 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 UUID generation functions. | | `CREATE EXTENSION "vector"` | Partial | PostgreSQL Extension | CockroachDB supports the `VECTOR` data type, vector indexes, and `pgvector`-compatible vector functions. | | `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 secondary indexes, GIN indexes, trigram indexes, or spatial indexes. | | `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 fully qualified names, subject to the limitations for cross-database object references. | | `CREATE EXTENSION "citext"` | ✗ | PostgreSQL Extension | CockroachDB does not support the PostgreSQL `citext` extension. To store and compare case-insensitive text, use the native `CITEXT` data type. For expression-based 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 statement diagnostics and SQL observability features instead. | ### 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 | Partial | Common Extension | Stored procedures documentation | | Cursors | Partial | Standard | Cursors documentation | | Triggers | Partial | Standard | Triggers documentation | | 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 |