Re: Why cannot alter a column's type when it's used by a generated column - Mailing list pgsql-hackers

On Fri, Oct 17, 2025 at 10:04 AM Chao Li <li.evan.chao@gmail.com> wrote:
> ```
> evantest=# create table abc (a int, b int generated always as (a+c) stored, c int);
> CREATE TABLE
> evantest=# insert into abc (a, c) values (1, 2);
> INSERT 0 1
> evantest=# select * from abc;
>  a | b | c
> ---+---+---
>  1 | 3 | 2
> (1 row)
> evantest=# alter table abc alter column a set data type bigint;
> ERROR:  cannot alter type of a column used by a generated column
> DETAIL:  Column "a" is used by generated column "b".
> ```
>
> ...
>
> Before proposing a patch, I would to like hear what hackers think about that.
>
in RememberAllDependentForRebuilding

                        /*
                         * This must be a reference from the expression of a
                         * generated column elsewhere in the same table.
                         * Changing the type/generated expression of a column
                         * that is used by a generated column is not allowed
                         * by SQL standard, so just punt for now.  It might be
                         * doable with some thinking and effort.
                         */
                        if (subtype == AT_AlterColumnType)
                            ereport(ERROR,
                                    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                                     errmsg("cannot alter type of a
column used by a generated column"),
                                     errdetail("Column \"%s\" is used
by generated column \"%s\".",
                                               colName,
                                               get_attname(col.objectId,
                                                           col.objectSubId,
                                                           false))));

The error code is ERRCODE_FEATURE_NOT_SUPPORTED.
the above comment said "It might be doable with some thinking and effort."

The attached patch removes this restriction.
it need more polish, but it's good enough to use it to verify the bug I reported
on
https://postgr.es/m/CACJufxHZsgn3zM5g-x7YmtFGzNDnRwR07S+GYfiUs+tZ45MDDw@mail.gmail.com