The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/sql-altertable.html
Description:
In the many forms of ALTER TABLE, described in
https://www.postgresql.org/docs/current/sql-altertable.html, it appears that
a generated column, defined with generated always as (expression) stored,
cannot have its generation expression updated.
There is a DROP EXPRESSION option that can remove the generation expression,
but no option to add back the generation expression with a different
definition.
My documentation recommendation is to be explicit about this limitation in
both the ALTER TABLE web page and the page that discusses generated columns,
https://www.postgresql.org/docs/15/ddl-generated-columns.html.
Dropping the column and recreating it (1) to preserve column order would
also require, I believe, dropping and recreating subsequent columns in the
same table; (2) often requires dropping and recreating objects that
reference the column unless there is a technique I have overlooked; (3)
which can, for the PG database I support, require dropping and recreating a
stack of dozens of SQL objects including materialized views that contain
much of our content.
So my related functionality recommendation is to add the option to ALTER
TABLE to assign the generation expression. (The term generated can also
involve identity columns or sequence references; I am not suggesting any
changes to that functionality.)