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
JSONBdata. When the table's primary information is stored inJSONB, it's useful to index a particular field of theJSONBdocument. In particular, computed columns allow for the following use case: a two-column table with aPRIMARY KEYcolumn and apayloadcolumn, whose primary key is computed as some field from thepayloadcolumn. This alleviates the need to manually separate your primary keys from your JSON blobs. For more information, see theJSONBexample 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,
FAMILYdefinition, inCHECKconstraints, orFOREIGN KEYconstraints. - 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
New in 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
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 a JSONB column and a stored computed column
In this example, create a table with a JSONB column and a stored computed column:
> CREATE TABLE student_profiles (
id STRING PRIMARY KEY AS (profile->>'id') STORED,
profile JSONB
);
Create a compute column after you create a table:
> ALTER TABLE student_profiles ADD COLUMN age INT AS ( (profile->>'age')::INT) STORED;
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 | age |
---------+---------------------------------------------------------------------------------------------------------------------+------+
| d78236 | {"age": "16", "credits": 120, "id": "d78236", "name": "Arthur Read", "school": "PVPHS", "sports": "none"} | 16 |
| f98112 | {"age": "15", "clubs": "MUN", "credits": 67, "id": "f98112", "name": "Buster Bunny", "school": "THS"} | 15 |
| t63512 | {"clubs": "Chess", "id": "t63512", "name": "Ernie Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} | NULL |
+--------+---------------------------------------------------------------------------------------------------------------------+------|
The primary key id is computed as a field from the profile column. Additionally the age column is computed from the profile column data as well.
This example shows how add a stored computed column with a coerced type:
CREATE TABLE json_data (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
json_info JSONB
);
INSERT INTO json_data (json_info) VALUES ('{"amount": "123.45"}');
ALTER TABLE json_data ADD COLUMN amount DECIMAL AS ((json_info->>'amount')::DECIMAL) STORED;
SELECT * FROM json_data;
id | json_info | amount
---------------------------------------+----------------------+---------
e7c3d706-1367-4d77-bfb4-386dfdeb10f9 | {"amount": "123.45"} | 123.45
(1 row)
Create a virtual computed column using JSONB data
In this example, create a table with a JSONB column and virtual computed columns:
> CREATE TABLE student_profiles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
profile JSONB,
full_name STRING AS (concat_ws(' ',profile->>'firstName', profile->>'lastName')) VIRTUAL,
birthday TIMESTAMP AS (parse_timestamp(profile->>'birthdate')) VIRTUAL
);
Then, insert a few rows of data:
> INSERT INTO student_profiles (profile) VALUES
('{"id": "d78236", "firstName": "Arthur", "lastName": "Read", "birthdate": "2010-01-25", "school": "PVPHS", "credits": 120, "sports": "none"}'),
('{"firstName": "Buster", "lastName": "Bunny", "birthdate": "2011-11-07", "id": "f98112", "school": "THS", "credits": 67, "clubs": "MUN"}'),
('{"firstName": "Ernie", "lastName": "Narayan", "school" : "Brooklyn Tech", "id": "t63512", "sports": "Track and Field", "clubs": "Chess"}');
> SELECT * FROM student_profiles;
id | profile | full_name | birthday
---------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------------------
0e420282-105d-473b-83e2-3b082e7033e4 | {"birthdate": "2011-11-07", "clubs": "MUN", "credits": 67, "firstName": "Buster", "id": "f98112", "lastName": "Bunny", "school": "THS"} | Buster Bunny | 2011-11-07 00:00:00
6e9b77cd-ec67-41ae-b346-7b3d89902c72 | {"birthdate": "2010-01-25", "credits": 120, "firstName": "Arthur", "id": "d78236", "lastName": "Read", "school": "PVPHS", "sports": "none"} | Arthur Read | 2010-01-25 00:00:00
f74b21e3-dc1e-49b7-a648-3c9b9024a70f | {"clubs": "Chess", "firstName": "Ernie", "id": "t63512", "lastName": "Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} | Ernie Narayan | NULL
(3 rows)
Time: 2ms total (execution 2ms / network 0ms)
The virtual column full_name is computed as a field from the profile column's data. The first name and last name are concatenated and separated by a single whitespace character using the concat_ws string function.
The virtual column birthday is parsed as a TIMESTAMP value from the profile column's birthdate string value. The parse_timestamp function is used to parse strings in TIMESTAMP format.
Create a table with a secondary index on a computed column
In this example, create a table with a virtual computed column 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) VIRTUAL,
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 virtual computed column to the table:
> ALTER TABLE x ADD COLUMN d INT AS (a // 2) VIRTUAL;
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)
Alter the formula for a computed column
To alter the formula for a computed column, you must DROP and ADD the column back with the new definition. Take the following table for instance:
> 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)
);
CREATE TABLE
Time: 4ms total (execution 4ms / network 0ms)
Add a computed column d:
> ALTER TABLE x ADD COLUMN d INT AS (a // 2) STORED;
ALTER TABLE
Time: 199ms total (execution 199ms / network 0ms)
If you try to alter it, you'll get an error:
> ALTER TABLE x ALTER COLUMN d INT AS (a // 3) STORED;
invalid syntax: statement ignored: at or near "int": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
ALTER TABLE x ALTER COLUMN d INT AS (a // 3) STORED
^
HINT: try \h ALTER TABLE
However, you can drop it and then add it with the new definition:
> SET sql_safe_updates = false;
> ALTER TABLE x DROP COLUMN d;
> ALTER TABLE x ADD COLUMN d INT AS (a // 3) STORED;
> SET sql_safe_updates = true;
SET
Time: 1ms total (execution 0ms / network 0ms)
ALTER TABLE
Time: 195ms total (execution 195ms / network 0ms)
ALTER TABLE
Time: 186ms total (execution 185ms / network 0ms)
SET
Time: 0ms total (execution 0ms / network 0ms)
See also
Was this helpful?
