Re: Why cannot alter a column's type when it's used by a generated column

On Tue, Oct 21, 2025 at 3:03 PM Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
>
> Here I am just proposing a patch for a small step. When any dependent generated column has SET EXPRESSION, then we can allow the column type change:
>
> ```
> -- Before this patch, the recommended usage was:
> ALTER TABLE gtest
> DROP COLUMN x,
> ALTER COLUMN a TYPE float8,
> ADD COLUMN x bigint GENERATED ALWAYS AS ((a::int + b) * 2) STORED;
>
> -- With this patch, the statement is simplified as:
> ALTER TABLE gtest
> ALTER COLUMN a TYPE float8,
> ALTER COLUMN x SET EXPRESSION ((a::int + b) * 2);
> ```
hi.

please feel free to bump the attached patch version.

+-- So, you can change a column's type as long as any dependent generated
+-- column already has a set expression defined:
+ALTER TABLE gtest27
+ ALTER COLUMN a TYPE float8,
+ ALTER COLUMN x SET EXPRESSION AS ((a::int + b) * 2);
+\d gtest27
+ Table "generated_stored_tests.gtest27"
+ Column | Type | Collation | Nullable |
Default
+--------+------------------+-----------+----------+---------------------------------------------------
+ a | double precision | | |
+ b | bigint | | |
+ x | bigint | | | generated always
as ((a::integer + b) * 2) stored
+

the above output seems wrong?
one way to quickly test it is create table gtest27 again
(
create table gtest27(a double precision, b bigint, c bigint GENERATED
ALWAYS as ((a +b) * 2) stored);
)
and the result of "\d gtest27" is

Column | Type | Collation | Nullable |
Default
--------+------------------+-----------+----------+--------------------------------------------------------------------------------
a | double precision | | |
b | bigint | | |
c | bigint | | | generated always
as (((a + b::double precision) * 2::double precision)) stored

which conflicts with your changes.