computed-columns.md

title	summary	toc
Use Computed Columns	A computed column exposes data generated by an expression included in the column definition.	true

A computed column exposes data generated from other columns by a scalar expression included in the column definition. A stored computed column (set with the STORED SQL keyword) is calculated when a row is inserted or updated, and stores the resulting value of the scalar expression in the primary index similar to a regular column. A virtual computed column (set with the VIRTUAL SQL keyword) is not stored, and the value of the scalar expression is computed during queries as needed.

Why use computed columns?

Computed columns are especially useful when used with JSONB columns or secondary indexes.

• JSONB columns are used for storing semi-structured JSONB data. When the table's primary information is stored in JSONB, it's useful to index a particular field of the JSONB document. In particular, computed columns allow for the following use case: a two-column table with a PRIMARY KEY column and a payload column, whose primary key is computed as some field from the payload column. This alleviates the need to manually separate your primary keys from your JSON blobs. For more information, see the JSONB example below.

• Secondary indexes can be created on computed columns, which is especially useful when a table is frequently sorted. See the secondary indexes example below.

Considerations

Computed columns:

• Cannot be used to generate other computed columns.
• Behave like any other column, with the exception that they cannot be written to directly.
• Are mutually exclusive with DEFAULT.

Virtual computed columns:

• Are not stored in the table's primary index.
• Are recomputed as the column data in the expression changes.
• Cannot be used as part of a primary key, FAMILY definition, in CHECK constraints, or FOREIGN KEY constraints.
• Cannot be a foreign key reference.
• Cannot be stored in indexes.
• Can be index columns.

Once a computed column is created, you cannot directly alter the formula. To make modifications to a computed column's formula, see the example below.

Creation

To define a stored computed column, use the following syntax:

column_name <type> AS (<expr>) STORED

{% include_cached new-in.html version="v21.1" %} To define a virtual computed column, use the following syntax:

column_name <type> AS (<expr>) VIRTUAL

Parameter	Description
column_name	The name/identifier of the computed column.
<type>	The data type of the computed column.
<expr>	The immutable scalar expression used to compute column values. You cannot use any functions, such as now() or nextval(), that are not immutable.
STORED	(Required for stored computed columns) The computed column is stored alongside other columns.
VIRTUAL	(Required for virtual columns) The computed column is virtual, meaning the column data is not stored in the table's primary index.

For compatibility with PostgresSQL, CockroachDB also supports creating computed columns with the syntax column_name <type> GENERATED ALWAYS AS (<expr>) STORED.

Examples

Create a table with a stored computed column

{% include {{ page.version.version }}/computed-columns/simple.md %}

Create a table with a JSONB column and a stored computed column

{% include {{ page.version.version }}/computed-columns/jsonb.md %}

Create a virtual computed column using JSONB data

{% include {{ page.version.version }}/computed-columns/virtual.md %}

Create a table with a secondary index on a computed column

{% include {{ page.version.version }}/computed-columns/secondary-index.md %}

Add a computed column to an existing table

{% include {{ page.version.version }}/computed-columns/add-computed-column.md %}

For more information, see ADD COLUMN.

Convert a computed column into a regular column

{% include {{ page.version.version }}/computed-columns/convert-computed-column.md %}

Alter the formula for a computed column

{% include {{ page.version.version }}/computed-columns/alter-computed-column.md %}

See also

• Scalar Expressions
• Information Schema
• CREATE TABLE
• JSONB