CREATE OR REPLACE FUNCTION generate_foo_immutable ()
RETURNS TRIGGER
AS $$
BEGIN
NEW.foo = NEW.bar * 2;
RETURN new;
END;
$$
LANGUAGE plpgsql IMMUTABLE;
CREATE TRIGGER generate_foo_immutable_trigger
BEFORE INSERT OR UPDATE [OF other_column1, other_column2, ...] ON foo_table
FOR EACH ROW
EXECUTE PROCEDURE public.generate_foo_immutable();
reference: https://stackoverflow.com/questions/77852268/how-to-add-a-stored-generated-column-to-a-very-large-table
tldr, when adding a generated stored column to an existing large table, it will lock the table so it can calculate the value for each row.
There seems to be a workaround:
BEFORE INSERT OR UPDATE
with a function that is equivalent to what you would put as the as generated expression.However, there seems to be a trade-off in that INSERT times are slower with a triggered function. source (4yrs old, for postgres v12): https://www.ongres.com/blog/generate_columns_vs_triggers/