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.evan.chao@gmail.com> wrote:
>
> Here I am just proposing a patch for a small step. When any dependent generated column has SET EXPRESSION, then we
canallow 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.