> ## 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

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](/docs/stable/array) |
| `AUTO INCREMENT` (Automatic key generation) | Alternative | Common Extension | [Automatic key generation FAQ](/docs/stable/sql-faqs.md#how-do-i-auto-generate-unique-row-ids-in-cockroachdb) |
| `BIT` | ✓ | Standard | [`BIT` documentation](/docs/stable/bit) |
| `BOOLEAN` | ✓ | Standard | [`BOOL` documentation](/docs/stable/bool) |
| `BYTES` | ✓ | CockroachDB Extension | [`BYTES` documentation](/docs/stable/bytes) |
| `COLLATE` | ✓ | Standard | [`COLLATE` documentation](/docs/stable/collate) |
| `DATE` | ✓ | Standard | [`DATE` documentation](/docs/stable/date) |
| `DECIMAL`, `NUMERIC` | ✓ | Standard | [`DECIMAL` documentation](/docs/stable/decimal) |
| `DOMAIN` | ✓ | Standard | [`DOMAIN` documentation](/docs/stable/domain) |
| `ENUM` | ✓ | PostgreSQL Extension | [`ENUM` documentation](/docs/stable/enum) |
| `FLOAT`, `REAL` | ✓ | Standard | [`FLOAT` documentation](/docs/stable/float) |
| `INET` | ✓ | PostgreSQL Extension | [`INET` documentation](/docs/stable/inet) |
| `INT` | ✓ | Standard | [`INT` documentation](/docs/stable/int) |
| `INTERVAL` | ✓ | Standard | [`INTERVAL` documentation](/docs/stable/interval) |
| `JSON`/`JSONB` | ✓ | Common Extension | [`JSONB` documentation](/docs/stable/jsonb) |
| `NULL` | ✓ | Standard | [`NULL`-handling documentation](/docs/stable/null-handling) |
| `OID` | ✓ | PostgreSQL Extension | [`OID` documentation](/docs/stable/oid) |
| `SERIAL` | ✓ | PostgreSQL Extension | [`SERIAL` documentation](/docs/stable/serial) |
| `SET` | ✗ | MySQL | Only allow rows to contain values from a defined set of terms. |
| `STRING`, `CHARACTER` | ✓ | Standard | [`STRING` documentation](/docs/stable/string) |
| `TIME` | ✓ | Standard | [`TIME` documentation](/docs/stable/time) |
| `TIMESTAMP`/`TIMESTAMPTZ` | ✓ | Standard | [`TIMESTAMP` documentation](/docs/stable/timestamp) |
| `TSQUERY` | ✓ | Standard | [`TSQUERY` documentation](/docs/stable/tsquery) |
| `TSVECTOR` | ✓ | Standard | [`TSVECTOR` documentation](/docs/stable/tsvector) |
| `UNSIGNED INT` | ✗ | Common Extension | `UNSIGNED INT` causes numerous casting issues, so we do not plan to support it. |
| `UUID` | ✓ | PostgreSQL Extension | [`UUID` documentation](/docs/stable/uuid) |
| Identifiers | ✓ | Standard | [Identifiers documentation](/docs/stable/keywords-and-identifiers.md#identifiers).  See also [SQL Name Resolution](/docs/stable/sql-name-resolution.md). |
| Key-value pairs | Alternative | Extension | [Key-Value FAQ](/docs/stable/sql-faqs.md#can-i-use-cockroachdb-as-a-key-value-store) |
| 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](/docs/stable/not-null.md) |
| Unique | ✓ | Standard | [Unique documentation](/docs/stable/unique.md) |
| Primary Key | ✓ | Standard | [Primary Key documentation](/docs/stable/primary-key.md) |
| Check | ✓ | Standard | [Check documentation](/docs/stable/check.md) |
| Foreign Key | ✓ | Standard | [Foreign Key documentation](/docs/stable/foreign-key.md) |
| Default Value | ✓ | Standard | [Default Value documentation](/docs/stable/default-value.md) |

### Transactions

| Component | Supported | Type | Details |
| - | - | - | - |
| Transactions (ACID semantics) | ✓ | Standard | [Transactions documentation](/docs/stable/transactions.md) |
| `BEGIN` | ✓ | Standard | [`BEGIN` documentation](/docs/stable/begin-transaction) |
| `COMMIT` | ✓ | Standard | [`COMMIT` documentation](/docs/stable/commit-transaction) |
| `ROLLBACK` | ✓ | Standard | [`ROLLBACK` documentation](/docs/stable/rollback-transaction) |
| `SAVEPOINT` | ✓ | Standard with CockroachDB extensions | CockroachDB supports nested transactions using [`SAVEPOINT`](/docs/stable/savepoint) |

### Indexes

| Component | Supported | Type | Details |
| - | - | - | - |
| Indexes | ✓ | Common Extension | [Indexes documentation](/docs/stable/indexes.md) |
| Multi-column indexes | ✓ | Common Extension | We do not limit on the number of columns indexes can include |
| Covering indexes | ✓ | Common Extension | [Storing Columns documentation](/docs/stable/create-index.md#store-columns) |
| GIN indexes | ✓ | Common Extension | [GIN indexes documentation](/docs/stable/inverted-indexes.md) |
| Trigram indexes | ✓ | PostgreSQL Extension | [Trigram indexes documentation](/docs/stable/trigram-indexes.md) |
| Partial indexes | ✓ | Common Extension | [Partial indexes documentation](/docs/stable/partial-indexes.md) |
| Spatial indexes | ✓ | Common Extension | [Spatial indexes documentation](/docs/stable/spatial-indexes.md) |
| Multiple indexes per query | Partial | Common Extension | [Index selection](/docs/stable/indexes.md#selection) |
| Full-text indexes | ✓ | Common Extension | [Full-text search documentation](/docs/stable/full-text-search.md) |
| Expression indexes | ✓ | Common Extension | [Expression indexes](/docs/stable/expression-indexes.md) |
| Prefix indexes | ✗ | Common Extension | Implement using [Expression indexes](/docs/stable/expression-indexes.md) |
| Hash indexes | ✗ | Common Extension | Improves performance of queries looking for single, exact values |
| Hash-sharded indexes | ✓ | CockroachDB Extension | [Hash-sharded Indexes documentation](/docs/stable/hash-sharded-indexes.md) |

### Schema changes

| Component | Supported | Type | Details |
| - | - | - | - |
| `ALTER TABLE` | ✓ | Standard | [`ALTER TABLE` documentation](/docs/stable/alter-table) |
| Database renames | ✓ | Standard | [`ALTER DATABASE ... RENAME TO` documentation](/docs/stable/alter-database#rename-to) |
| Table renames | ✓ | Standard | [`ALTER TABLE ... RENAME TO` documentation](/docs/stable/alter-table#rename-to) |
| Column renames | ✓ | Standard | [`RENAME COLUMN` documentation](/docs/stable/alter-table#rename-column) |
| Altering a column's data type | ✓ | Standard | [`ALTER COLUMN` documentation](/docs/stable/alter-table#alter-column-data-types) |
| Adding columns | ✓ | Standard | [`ADD COLUMN` documentation](/docs/stable/alter-table#add-column) |
| Removing columns | ✓ | Standard | [`DROP COLUMN` documentation](/docs/stable/alter-table#drop-column) |
| Adding constraints | ✓ | Standard | [`ADD CONSTRAINT` documentation](/docs/stable/alter-table#add-constraint) |
| Removing constraints | ✓ | Standard | [`DROP CONSTRAINT` documentation](/docs/stable/alter-table#drop-constraint) |
| Index renames | ✓ | Standard | [`ALTER INDEX ... RENAME TO` documentation](/docs/stable/alter-index#rename-to) |
| Adding indexes | ✓ | Standard | [`CREATE INDEX` documentation](/docs/stable/create-index) |
| Removing indexes | ✓ | Standard | [`DROP INDEX` documentation](/docs/stable/drop-index) |
| Altering a primary key | ✓ | Standard | [`ALTER PRIMARY KEY` documentation](/docs/stable/alter-table#alter-primary-key) |
| Adding user-defined schemas | ✓ | Standard | [`CREATE SCHEMA` documentation](/docs/stable/create-schema) |
| Removing user-defined schemas | ✓ | Standard | [`DROP SCHEMA` documentation](/docs/stable/drop-schema) |
| Altering user-defined schemas | ✓ | Standard | [`ALTER SCHEMA` documentation](/docs/stable/create-schema) |

### Statements

| Component | Supported | Type | Details |
| - | - | - | - |
| Common statements | ✓ | Standard, PostgreSQL/CockroachDB Extension | [SQL Statements documentation](/docs/stable/sql-statements.md) |
| `UPSERT` | ✓ | PostgreSQL, MSSQL Extension | [`UPSERT` documentation](/docs/stable/upsert) |
| `EXPLAIN` | ✓ | Common Extension | [`EXPLAIN` documentation](/docs/stable/explain) |
| `SELECT ... INTO` | ✓ | Common Extension | [PL/pgSQL documentation](/docs/stable/plpgsql.md#declare-a-variable). |
| `SELECT FOR UPDATE` | ✓ | Common Extension | [`SELECT FOR UPDATE` documentation](/docs/stable/select-for-update) |

### Clauses

| Component | Supported | Type | Details |
| - | - | - | - |
| Common clauses | ✓ | Standard | [SQL Grammar documentation](/docs/stable/sql-grammar.md) |
| `LIMIT` | ✓ | Common Extension | Limit the number of rows a statement returns. For more information, see [Limit Query Results](/docs/stable/limit-offset.md). |
| `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](/docs/stable/limit-offset.md). |
| `RETURNING` | ✓ | Common Extension | Retrieve a table of rows statements affect. For examples, see the [`INSERT`](/docs/stable/insert) and [`DELETE`](/docs/stable/delete) documentation. |

### Table expressions

| Component | Supported | Type | Details |
| - | - | - | - |
| Table and View references | ✓ | Standard | [Table expressions documentation](/docs/stable/table-expressions.md#table-and-view-names) |
| `AS` in table expressions | ✓ | Standard | [Aliased table expressions documentation](/docs/stable/table-expressions.md#aliased-table-expressions) |
| `JOIN` (`INNER`, `LEFT`, `RIGHT`, `FULL`, `CROSS`) | ✓ | Standard | [`JOIN` expressions documentation](/docs/stable/table-expressions#join-expressions) |
| Sub-queries as table expressions | Partial | Standard | Non-correlated subqueries are [supported](/docs/stable/table-expressions.md#use-a-subquery), as are most [correlated subqueries](/docs/stable/subqueries.md#correlated-subqueries). |
| Table generator functions | Partial | PostgreSQL Extension | [Table generator functions documentation](/docs/stable/table-expressions.md#table-generator-functions) |
| `WITH ORDINALITY` | ✓ | CockroachDB Extension | [Ordinality annotation documentation](/docs/stable/table-expressions.md#ordinality-annotation) |

### Scalar expressions and Boolean formulas

| Component | Supported | Type | Details |
| - | - | - | - |
| Common functions | ✓ | Standard | [Functions calls and SQL special forms documentation](/docs/stable/scalar-expressions.md#function-calls-and-sql-special-forms) |
| Common operators | ✓ | Standard | [Operators documentation](/docs/stable/scalar-expressions.md#unary-and-binary-operations) |
| `IF`/`CASE`/`NULLIF` | ✓ | Standard | [Conditional expressions documentation](/docs/stable/scalar-expressions.md#conditional-expressions) |
| `COALESCE`/`IFNULL` | ✓ | Standard | [Conditional expressions documentation](/docs/stable/scalar-expressions.md#conditional-expressions) |
| `AND`/`OR` | ✓ | Standard | [Conditional expressions documentation](/docs/stable/scalar-expressions.md#conditional-expressions) |
| `LIKE`/`ILIKE` | ✓ | Standard | [String pattern matching documentation](/docs/stable/scalar-expressions.md#string-pattern-matching) |
| `SIMILAR TO` | ✓ | Standard | [SQL regexp pattern matching documentation](/docs/stable/scalar-expressions.md#string-matching-using-sql-regular-expressions) |
| Matching using POSIX regular expressions | ✓ | Common Extension | [POSIX regexp pattern matching documentation](/docs/stable/scalar-expressions.md#string-matching-using-posix-regular-expressions) |
| `EXISTS` | Partial | Standard | Non-correlated subqueries are [supported](/docs/stable/scalar-expressions.md#existence-test-on-the-result-of-subqueries), as are most [correlated subqueries](/docs/stable/subqueries.md#correlated-subqueries). Works only with small data sets. |
| Scalar subqueries | Partial | Standard | Non-correlated subqueries are [supported](/docs/stable/scalar-expressions.md#scalar-subqueries), as are most [correlated subqueries](/docs/stable/subqueries.md#correlated-subqueries). Works only with small data sets. |
| Bitwise arithmetic | ✓ | Common Extension | [Operators documentation](/docs/stable/scalar-expressions.md#unary-and-binary-operations) |
| Array constructors and subscripting | Partial | PostgreSQL Extension | Array expression documentation: [Constructor syntax](/docs/stable/scalar-expressions.md#array-constructors) and [Subscripting](/docs/stable/scalar-expressions.md#subscripted-expressions) |
| `COLLATE` | ✓ | Standard | [Collation expressions documentation](/docs/stable/scalar-expressions.md#collation-expressions) |
| Column ordinal references | ✓ | CockroachDB Extension | [Column references documentation](/docs/stable/scalar-expressions.md#column-references) |
| Type annotations | ✓ | CockroachDB Extension | [Type annotations documentation](/docs/stable/scalar-expressions.md#explicitly-typed-expressions) |

### Permissions

| Component | Supported | Type | Details |
| - | - | - | - |
| Users | ✓ | Standard | [Users documentation](/docs/stable/security-reference/authorization.md#sql-users) |
| Roles | ✓ | Standard | [Roles documentation](/docs/stable/security-reference/authorization.md#roles) |
| Object ownership | ✓ | Common Extension | [Ownership documentation](/docs/stable/security-reference/authorization.md#object-ownership) |
| Privileges | ✓ | Standard | [Privileges documentation](/docs/stable/security-reference/authorization.md#managing-privileges) |
| Default privileges | ✓ | PostgreSQL Extension | [Default privileges documentation](/docs/stable/security-reference/authorization.md#default-privileges) |

### 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](/docs/stable/spatial-data-overview.md), [spatial indexes](/docs/stable/spatial-indexes.md), and many [PostGIS-compatible spatial functions](/docs/stable/functions-and-operators.md). Not all PostGIS functions are supported. |
| `CREATE EXTENSION "pg_trgm"` | Partial | PostgreSQL Extension | CockroachDB supports [trigram indexes](/docs/stable/trigram-indexes.md) 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()`](/docs/stable/functions-and-operators), [`dmetaphone_alt()`](/docs/stable/functions-and-operators), and [`daitch_mokotoff()`](/docs/stable/functions-and-operators). |
| `CREATE EXTENSION "pgcrypto"` | Partial | PostgreSQL Extension | CockroachDB supports some PostgreSQL-compatible [cryptographic functions](/docs/stable/functions-and-operators.md), including `crypt()`, `digest()`, `gen_random_uuid()`, `gen_salt()`, and `hmac()`. CockroachDB also supports [column-level encryption functions](/docs/stable/column-level-encryption.md) 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](/docs/stable/functions-and-operators.md). |
| `CREATE EXTENSION "vector"` | Partial | PostgreSQL Extension | CockroachDB supports the [`VECTOR`](/docs/stable/vector) data type, [vector indexes](/docs/stable/vector-indexes.md), and `pgvector`-compatible [vector functions](/docs/stable/functions-and-operators.md). |
| `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](/docs/stable/create-index.md), [GIN indexes](/docs/stable/inverted-indexes.md), [trigram indexes](/docs/stable/trigram-indexes.md), or [spatial indexes](/docs/stable/spatial-indexes.md). |
| `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](/docs/stable/sql-name-resolution.md#lookup-with-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`](/docs/stable/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](/docs/stable/ui-statements-page.md) and SQL observability features instead. |

### Miscellaneous

| Component | Supported | Type | Details |
| - | - | - | - |
| Column families | ✓ | CockroachDB Extension | [Column Families documentation](/docs/stable/column-families.md) |
| Computed columns (stored and virtual) | ✓ | Common Extension | [Computed Columns documentation](/docs/stable/computed-columns.md) |
| `ON UPDATE` expressions | ✓ | MySQL Extension | [`ON UPDATE` expressions documentation](/docs/stable/create-table#on-update-expressions) |
| Multi-region capabilities | ✓ | CockroachDB Extension | [Multi-region documentation](/docs/stable/multiregion-overview.md) |
| System catalog schemas | ✓ | Standard, PostgreSQL/CockroachDB Extension | [`crdb_internal`](/docs/stable/crdb-internal) (CockroachDB Extension) [`information_schema`](/docs/stable/information-schema) (Standard) [`pg_catalog`](/docs/stable/pg-catalog) (PostgreSQL Extension) [`pg_extension`](/docs/stable/pg-extension) (PostgreSQL Extension) |
| Sequences | ✓ | Common Extension | [`CREATE SEQUENCE` documentation](/docs/stable/create-sequence) |
| Identity columns | ✓ | Common Extension | [Identity columns documentation](/docs/stable/create-table.md#identity-columns) |
| Views | ✓ | Standard | [Views documentation](/docs/stable/views.md) |
| Materialized views | ✓ | Common Extension | [Materialized views documentation](/docs/stable/views.md#materialized-views) |
| Window functions | ✓ | Standard | [Window Functions documentation](/docs/stable/window-functions.md) |
| Common table expressions | Partial | Common Extension | [Common Table Expressions documentation](/docs/stable/common-table-expressions.md) |
| Stored procedures | Partial | Common Extension | [Stored procedures documentation](/docs/stable/stored-procedures.md) |
| Cursors | Partial | Standard | [Cursors documentation](/docs/stable/cursors.md) |
| Triggers | Partial | Standard | [Triggers documentation](/docs/stable/triggers.md) |
| Row-level TTL | ✓ | Common Extension | Automatically delete expired rows.  For more information, see [Batch-delete expired data with Row-Level TTL](/docs/stable/row-level-ttl.md). |
| User-defined functions | Partial | Standard | [User-Defined Functions documentation](/docs/stable/user-defined-functions.md) |