# Computed Columns

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

To define a computed column, 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

In this example, let's create a simple table with a computed column:

    > CREATE TABLE users (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            city STRING,
            first_name STRING,
            last_name STRING,
            full_name STRING AS (CONCAT(first_name, ' ', last_name)) STORED,
            address STRING,
            credit_card STRING,
            dl STRING UNIQUE CHECK (LENGTH(dl) < 8)
    );

Then, insert a few rows of data:

    > INSERT INTO users (first_name, last_name) VALUES
        ('Lola', 'McDog'),
        ('Carl', 'Kimball'),
        ('Ernie', 'Narayan');

    > SELECT * FROM users;

                       id                  | city | first_name | last_name |   full_name   | address | credit_card |  dl
    +--------------------------------------+------+------------+-----------+---------------+---------+-------------+------+
      5740da29-cc0c-47af-921c-b275d21d4c76 | NULL | Ernie      | Narayan   | Ernie Narayan | NULL    | NULL        | NULL
      e7e0b748-9194-4d71-9343-cd65218848f0 | NULL | Lola       | McDog     | Lola McDog    | NULL    | NULL        | NULL
      f00e4715-8ca7-4d5a-8de5-ef1d5d8092f3 | NULL | Carl       | Kimball   | Carl Kimball  | NULL    | NULL        | NULL
    (3 rows)

The `full_name` column is computed from the `first_name` and `last_name` columns without the need to define a view.

### Create a table with geo-partitions and a computed column

Note:
Partioning is an enterprise feature. To request and enable a trial or full enterprise license, see Enterprise Licensing.

In this example, create a table with geo-partitioning and a computed column:

    > CREATE TABLE user_locations (
        locality STRING AS (CASE
          WHEN country IN ('ca', 'mx', 'us') THEN 'north_america'
          WHEN country IN ('au', 'nz') THEN 'australia'
        END) STORED,
        id SERIAL,
        name STRING,
        country STRING,
        PRIMARY KEY (locality, id))
        PARTITION BY LIST (locality)
        (PARTITION north_america VALUES IN ('north_america'),
        PARTITION australia VALUES IN ('australia'));

Then, insert a few rows of data:

    > INSERT INTO user_locations (name, country) VALUES
        ('Leonard McCoy', 'us'),
        ('Uhura', 'nz'),
        ('Spock', 'ca'),
        ('James Kirk', 'us'),
        ('Scotty', 'mx'),
        ('Hikaru Sulu', 'us'),
        ('Pavel Chekov', 'au');

    > SELECT * FROM user_locations;

    +---------------+--------------------+---------------+---------+
    |   locality    |         id         |     name      | country |
    +---------------+--------------------+---------------+---------+
    | australia     | 333153890100609025 | Uhura         | nz      |
    | australia     | 333153890100772865 | Pavel Chekov  | au      |
    | north_america | 333153890100576257 | Leonard McCoy | us      |
    | north_america | 333153890100641793 | Spock         | ca      |
    | north_america | 333153890100674561 | James Kirk    | us      |
    | north_america | 333153890100707329 | Scotty        | mx      |
    | north_america | 333153890100740097 | Hikaru Sulu   | us      |
    +---------------+--------------------+---------------+---------+

The `locality` column is computed from the `country` column. The `locality` values can then be used for geo-partitioning.

### Create a table with a `JSONB` column and a computed column

In this example, create a table with a `JSONB` column and a computed column:

    > CREATE TABLE student_profiles (
        id STRING PRIMARY KEY AS (profile->>'id') STORED,
        profile JSONB
    );

Then, insert a few rows of data:

    > INSERT INTO student_profiles (profile) VALUES
        ('{"id": "d78236", "name": "Arthur Read", "age": "16", "school": "PVPHS", "credits": 120, "sports": "none"}'),
        ('{"name": "Buster Bunny", "age": "15", "id": "f98112", "school": "THS", "credits": 67, "clubs": "MUN"}'),
        ('{"name": "Ernie Narayan", "school" : "Brooklyn Tech", "id": "t63512", "sports": "Track and Field", "clubs": "Chess"}');

    > SELECT * FROM student_profiles;

    +--------+---------------------------------------------------------------------------------------------------------------------+
    |   id   |                                                       profile                                                       |
    +--------+---------------------------------------------------------------------------------------------------------------------+
    | d78236 | {"age": "16", "credits": 120, "id": "d78236", "name": "Arthur Read", "school": "PVPHS", "sports": "none"}           |
    | f98112 | {"age": "15", "clubs": "MUN", "credits": 67, "id": "f98112", "name": "Buster Bunny", "school": "THS"}               |
    | t63512 | {"clubs": "Chess", "id": "t63512", "name": "Ernie Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} |
    +--------+---------------------------------------------------------------------------------------------------------------------+

The primary key `id` is computed as a field from the `profile` column.

### Create a table with a secondary index on a computed column

In this example, create a table with a computed columns and an index on that column:

    > CREATE TABLE gymnastics (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        athlete STRING,
        vault DECIMAL,
        bars DECIMAL,
        beam DECIMAL,
        floor DECIMAL,
        combined_score DECIMAL AS (vault + bars + beam + floor) STORED,
        INDEX total (combined_score DESC)
      );

Then, insert a few rows a data:

    > INSERT INTO gymnastics (athlete, vault, bars, beam, floor) VALUES
        ('Simone Biles', 15.933, 14.800, 15.300, 15.800),
        ('Gabby Douglas', 0, 15.766, 0, 0),
        ('Laurie Hernandez', 15.100, 0, 15.233, 14.833),
        ('Madison Kocian', 0, 15.933, 0, 0),
        ('Aly Raisman', 15.833, 0, 15.000, 15.366);

    > SELECT * FROM gymnastics;

    +--------------------------------------+------------------+--------+--------+--------+--------+----------------+
    |                  id                  |     athlete      | vault  |  bars  |  beam  | floor  | combined_score |
    +--------------------------------------+------------------+--------+--------+--------+--------+----------------+
    | 3fe11371-6a6a-49de-bbef-a8dd16560fac | Aly Raisman      | 15.833 |      0 | 15.000 | 15.366 |         46.199 |
    | 56055a70-b4c7-4522-909b-8f3674b705e5 | Madison Kocian   |      0 | 15.933 |      0 |      0 |         15.933 |
    | 69f73fd1-da34-48bf-aff8-71296ce4c2c7 | Gabby Douglas    |      0 | 15.766 |      0 |      0 |         15.766 |
    | 8a7b730b-668d-4845-8d25-48bda25114d6 | Laurie Hernandez | 15.100 |      0 | 15.233 | 14.833 |         45.166 |
    | b2c5ca80-21c2-4853-9178-b96ce220ea4d | Simone Biles     | 15.933 | 14.800 | 15.300 | 15.800 |         61.833 |
    +--------------------------------------+------------------+--------+--------+--------+--------+----------------+

Now, run a query using the secondary index:

    > SELECT athlete, combined_score FROM gymnastics ORDER BY combined_score DESC;

    +------------------+----------------+
    |     athlete      | combined_score |
    +------------------+----------------+
    | Simone Biles     |         61.833 |
    | Aly Raisman      |         46.199 |
    | Laurie Hernandez |         45.166 |
    | Madison Kocian   |         15.933 |
    | Gabby Douglas    |         15.766 |
    +------------------+----------------+

The athlete with the highest combined score of 61.833 is Simone Biles.

### Add a computed column to an existing table

In this example, create a table:

    > CREATE TABLE x (
        a INT NULL,
        b INT NULL AS (a * 2) STORED,
        c INT NULL AS (a + 4) STORED,
        FAMILY "primary" (a, b, rowid, c)
      );

Then, insert a row of data:

    > INSERT INTO x VALUES (6);

    > SELECT * FROM x;

    +---+----+----+
    | a | b  | c  |
    +---+----+----+
    | 6 | 12 | 10 |
    +---+----+----+
    (1 row)

Now add another computed column to the table:

    > ALTER TABLE x ADD COLUMN d INT AS (a // 2) STORED;

The `d` column is added to the table and computed from the `a` column divided by 2.

    > SELECT * FROM x;

    +---+----+----+---+
    | a | b  | c  | d |
    +---+----+----+---+
    | 6 | 12 | 10 | 3 |
    +---+----+----+---+
    (1 row)

For more information, see `ADD COLUMN`.

### Convert a computed column into a regular column

You can convert a stored, computed column into a regular column by using `ALTER TABLE`.

In this example, create a simple table with a computed column:

    > CREATE TABLE office_dogs (
        id INT PRIMARY KEY,
        first_name STRING,
        last_name STRING,
        full_name STRING AS (CONCAT(first_name, ' ', last_name)) STORED
      );

Then, insert a few rows of data:

    > INSERT INTO office_dogs (id, first_name, last_name) VALUES
        (1, 'Petee', 'Hirata'),
        (2, 'Carl', 'Kimball'),
        (3, 'Ernie', 'Narayan');

    > SELECT * FROM office_dogs;

    +----+------------+-----------+---------------+
    | id | first_name | last_name |   full_name   |
    +----+------------+-----------+---------------+
    |  1 | Petee      | Hirata    | Petee Hirata  |
    |  2 | Carl       | Kimball   | Carl Kimball  |
    |  3 | Ernie      | Narayan   | Ernie Narayan |
    +----+------------+-----------+---------------+
    (3 rows)

The `full_name` column is computed from the `first_name` and `last_name` columns without the need to define a view. You can view the column details with the `SHOW COLUMNS` statement:

    > SHOW COLUMNS FROM office_dogs;

    +-------------+-----------+-------------+----------------+------------------------------------+-------------+
    | column_name | data_type | is_nullable | column_default |       generation_expression        |   indices   |
    +-------------+-----------+-------------+----------------+------------------------------------+-------------+
    | id          | INT       |    false    | NULL           |                                    | {"primary"} |
    | first_name  | STRING    |    true     | NULL           |                                    | {}          |
    | last_name   | STRING    |    true     | NULL           |                                    | {}          |
    | full_name   | STRING    |    true     | NULL           | concat(first_name, ' ', last_name) | {}          |
    +-------------+-----------+-------------+----------------+------------------------------------+-------------+
    (4 rows)

Now, convert the computed column (`full_name`) to a regular column:

    > ALTER TABLE office_dogs ALTER COLUMN full_name DROP STORED;

Check that the computed column was converted:

    > SHOW COLUMNS FROM office_dogs;

    +-------------+-----------+-------------+----------------+-----------------------+-------------+
    | column_name | data_type | is_nullable | column_default | generation_expression |   indices   |
    +-------------+-----------+-------------+----------------+-----------------------+-------------+
    | id          | INT       |    false    | NULL           |                       | {"primary"} |
    | first_name  | STRING    |    true     | NULL           |                       | {}          |
    | last_name   | STRING    |    true     | NULL           |                       | {}          |
    | full_name   | STRING    |    true     | NULL           |                       | {}          |
    +-------------+-----------+-------------+----------------+-----------------------+-------------+
    (4 rows)

The computed column is now a regular column and can be updated as such:

    > INSERT INTO office_dogs (id, first_name, last_name, full_name) VALUES (4, 'Lola', 'McDog', 'This is not computed');

    > SELECT * FROM office_dogs;

    +----+------------+-----------+----------------------+
    | id | first_name | last_name |      full_name       |
    +----+------------+-----------+----------------------+
    |  1 | Petee      | Hirata    | Petee Hirata         |
    |  2 | Carl       | Kimball   | Carl Kimball         |
    |  3 | Ernie      | Narayan   | Ernie Narayan        |
    |  4 | Lola       | McDog     | This is not computed |
    +----+------------+-----------+----------------------+
    (4 rows)

## See also

- Scalar Expressions
- Information Schema
- `CREATE TABLE`
- `JSONB`
- Define Table Partitions (Enterprise)