Skip to content

Latest commit

 

History

History
70 lines (45 loc) · 3.7 KB

computed-columns.md

File metadata and controls

70 lines (45 loc) · 3.7 KB
title summary toc
Computed Columns
A computed column stores data generated by an expression included in the column definition.
false

New in v2.0: A computed column stores data generated from other columns by a scalar expression included in the column definition.

Why use computed columns?

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

  • Partitioning requires that partitions are defined using columns that are a prefix of the primary key. In the case of geo-partitioning, some applications will want to collapse the number of possible values in this column, to make certain classes of queries more performant. For example, if a users table has a country and state column, then you can make a stored computed column locality with a reduced domain for use in partitioning. For more information, see the partitioning example below.

  • 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 added after a table is created. Follow the GitHub issue for updates on this limitation.
  • Cannot be used to generate other computed columns.
  • Cannot be a foreign key reference.
  • Behave like any other column, with the exception that they cannot be written to directly.
  • Are mutually exclusive with DEFAULT.

Creation

Computed columns can only be added at the time of table creation. Use the following syntax:

column_name <type> AS (<expr>) STORED
Parameter Description
column_name The name/identifier of the computed column.
<type> The data type of the computed column.
<expr> The pure scalar expression used to compute column values. Any functions marked as impure, such as now() or nextval() cannot be used.
STORED (Required) The computed column is stored alongside other columns.

Examples

Create a table with a computed column

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

Create a table with geo-partitions and a computed column

{% include computed-columns/partitioning.md %} The locality values can then be used for geo-partitioning.

Create a table with a JSONB column and a computed column

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

Create a table with a secondary index on a computed column

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

See Also