Search code examples
sqlclickhouse

Computed or generated column in Clickhouse

Is there any option how to create a computed column in the CREATE TABLE statement? Something like generated column in PostgreSQL

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

or computed column in T-SQL

CREATE TABLE dbo.Products
(
  ProductID int IDENTITY (1,1) NOT NULL
  , QtyAvailable smallint
  , UnitPrice money
  , InventoryValue AS QtyAvailable * UnitPrice
);

If there is no similar option, what could be the alternative please? My intention is to use these generated column in the materialized views without needs to compute them in MV create scripts.

Solution
    1. MATERIALIZED columns for storing computed values https://clickhouse.com/docs/en/sql-reference/statements/create/table/#materialized
    2. ALIAS columns to compute column values on the fly https://clickhouse.com/docs/en/sql-reference/statements/create/table/#alias