@Rocky can I add alter table add column based on function logic on existing column of table?

Yes, you can do this using Generated Columns, a feature introduced in StarRocks v3.1. When you add a generated column to an existing table, StarRocks automatically computes the values for existing rows based on your specified function/logic and stores them. Syntax

sql
ALTER TABLE <table_name> 
ADD COLUMN <new_column_name> <data_type> AS <expression>;

Example If you have a table with a JSON column and want to add a column that extracts a specific field as a string:

sql
ALTER TABLE orders 
ADD COLUMN customer_name VARCHAR(255) AS json_string(json_query(data_json, "$.name"));

Key Rules and Limitations * Automatic Population: For existing data, StarRocks will calculate and write the values for the new column without requiring you to rewrite the entire table. * Supported Models: Works on Duplicate, Primary Key, and Unique Key tables. It is not supported on Aggregate tables. * Expression Constraints: * The expression can reference multiple regular columns. * It cannot use aggregate functions (e.g.,

SUM

,

AVG

). * It cannot reference other generated columns or auto-increment columns. * Column Order: Generated columns must be placed after all regular columns in the schema. * Data Loading: Once added, you do not provide values for this column during inserts/loads; StarRocks calculates them automatically. For more details, see the Generated Columns documentation. References • sql-reference/sql-statements/generated_columns.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md

@Rocky If I add column now, does it populate and store this value for older data or will it process for older data and start storing for newer data only?

It populates and stores the value for both older (existing) data and new data. When you run the

ALTER TABLE ... ADD COLUMN ... AS <expression>

command, StarRocks performs the following: 1. For Older Data: StarRocks automatically scans your existing rows, calculates the result of the function/expression, and writes these values to the disk. _ _Optimization:* StarRocks is optimized for this; it doesn't rewrite the whole table. It only writes the data for the new column and links it to your existing files. 2. For Newer Data: Any new data loaded into the table after the column is added will have the value calculated and stored automatically at the time of ingestion. Example from the docs: If you have a row already in the table and then add a generated column:

sql
-- Existing row: id=1, data_json='{"a": 10}'

-- Add generated column
ALTER TABLE my_table 
ADD COLUMN a_val INT AS json_string(json_query(data_json, "$.a"));

-- Querying now shows the calculated value for the old row
SELECT * FROM my_table;
-- Result: 1, '{"a": 10}', 10

Things to keep in mind: * Storage: Because these are "stored" (materialized) generated columns, they will occupy additional disk space. * Performance: Adding the column to a very large table will trigger a background job to calculate values for all existing rows. You can monitor the progress using

SHOW ALTER TABLE COLUMN

. References • sql-reference/sql-statements/generated_columns.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • sql-reference/sql-statements/generated_columns.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md