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

Started by Chao Li3 months ago8 messages

li.evan.chao@gmail.com

3 months ago

Hi Hackers,

I noticed this problem while percolating in the other discussion.

```
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".
```

I understand that is to maintain data correctness and dependency integrity. Change a’s type might break the expression of generated column b.

Now, if I have to change the column type, I have to execute 3 statements:
* drop the generated column
* alter the column’s type
* create the generated column again

Which is inconvenient.

But look at this SQL:
```
evantest=# alter table abc alter column a set data type bigint, alter column b set data type bigint, alter column b set expression as (a*c), alter column c 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”.
```

If I explicitly update all columns’ type and explicitly set expression of b in the same command, which looks a reasonable operation. If the new expression doesn’t work, then the entire command will fail. This is similar to run the 3 statements in the same transaction.

So I think it would be reasonable to support that, when updating a column’s type that is used by generated columns, "set expression”s for all related generated columns explicitly present, then the "alter column type" should be allowed.

Before proposing a patch, I would to like hear what hackers think about that.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

jian he

jian.universality@gmail.com

3 months ago

In reply to: Chao Li (#1)

1 attachment(s)

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

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
/messages/by-id/CACJufxHZsgn3zM5g-x7YmtFGzNDnRwR07S+GYfiUs+tZ45MDDw@mail.gmail.com

Attachments:

v1-0001-SET-DATA-TYPE-cope-with-generation-expression-dependency.patchtext/x-patch; charset=US-ASCII; name=v1-0001-SET-DATA-TYPE-cope-with-generation-expression-dependency.patchDownload

From 7e48fdf35dc40261f50cff8f5f80b41db5c6d8d4 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 20 Oct 2025 15:45:03 +0800
Subject: [PATCH v1 1/1] SET DATA TYPE cope with generation expression
 dependency

let ALTER COLUMN SET DATA TYPE cope with generation expression dependency

demo:
CREATE TABLE main_table (a int, b int generated always as (a) stored);
ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8;
RROR:  cannot alter type of a column used by a generated column
DETAIL:  Column "a" is used by generated column "b".

With the attached patch, the previous error will no longer occur.

discussion: https://postgr.es/m/
related discussion: https://postgr.es/m/CACJufxHZsgn3zM5g-x7YmtFGzNDnRwR07S+GYfiUs+tZ45MDDw@mail.gmail.com
---
 src/backend/commands/tablecmds.c              |  90 +++++++++++++--
 src/backend/utils/adt/ruleutils.c             | 108 ++++++++++++++++++
 src/include/utils/ruleutils.h                 |   1 +
 .../regress/expected/generated_stored.out     |  59 ++++++++--
 .../regress/expected/generated_virtual.out    |  50 ++++++--
 src/test/regress/sql/generated_stored.sql     |  18 ++-
 src/test/regress/sql/generated_virtual.sql    |  13 ++-
 7 files changed, 301 insertions(+), 38 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fc89352b661..2dba15c0c0e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -208,6 +208,8 @@ typedef struct AlteredTableInfo
 	char	   *clusterOnIndex; /* index to use for CLUSTER */
 	List	   *changedStatisticsOids;	/* OIDs of statistics to rebuild */
 	List	   *changedStatisticsDefs;	/* string definitions of same */
+	List	   *changedGenerationExprOids; /* OIDs of generation expression to rebuild */
+	List	   *changedGenerationExprDefs; /* string definitions of same */
 } AlteredTableInfo;
 
 /* Struct describing one new constraint to check in Phase 3 scan */
@@ -650,6 +652,7 @@ static void RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableT
 											  Relation rel, AttrNumber attnum, const char *colName);
 static void RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab);
 static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab);
+static void RememberGenerationExpressionForRebuilding(Oid indoid, AlteredTableInfo *tab);
 static void RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab);
 static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
 								   LOCKMODE lockmode);
@@ -15171,20 +15174,12 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
 						/*
 						 * 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.
+						 * Changing the type of a column that is used by a
+						 * generated column is not allowed by SQL standard, but
+						 * we allow it.
 						 */
 						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))));
+							RememberGenerationExpressionForRebuilding(foundObject.objectId, tab);
 					}
 					break;
 				}
@@ -15367,6 +15362,32 @@ RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab)
 	}
 }
 
+/*
+ * Subroutine for ATExecAlterColumnType: remember that a generation expression object
+ * needs to be rebuilt (which we might already know).
+ */
+static void
+RememberGenerationExpressionForRebuilding(Oid attrdefoid, AlteredTableInfo *tab)
+{
+	/*
+	 * This de-duplication check is critical for two independent reasons: we
+	 * mustn't try to recreate the same generation expression twice, and if the
+	 * generation expression object depends on more than one column whose type
+	 * is to be altered, we must capture its definition string before applying
+	 * any of the type changes. ruleutils.c will get confused if we ask again
+	 * later.
+	 */
+	if (!list_member_oid(tab->changedGenerationExprOids, attrdefoid))
+	{
+		/* OK, capture the generation expression's existing definition string */
+		char	   *defstring = pg_get_generationdef(attrdefoid);
+
+		tab->changedGenerationExprOids = lappend_oid(tab->changedGenerationExprOids, attrdefoid);
+		tab->changedGenerationExprDefs = lappend(tab->changedGenerationExprDefs, defstring);
+	}
+}
+
+
 /*
  * Subroutine for ATExecAlterColumnType: remember that a statistics object
  * needs to be rebuilt (which we might already know).
@@ -15507,6 +15528,17 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
 		add_exact_object_address(&obj, objects);
 	}
 
+	/* add dependencies for new generation expression */
+	forboth(oid_item, tab->changedGenerationExprOids,
+			def_item, tab->changedGenerationExprDefs)
+	{
+		Oid			oldId = lfirst_oid(oid_item);
+
+		ATPostAlterTypeParse(oldId, tab->relid, InvalidOid,
+							 (char *) lfirst(def_item),
+							 wqueue, lockmode, tab->rewrite);
+	}
+
 	/* add dependencies for new statistics */
 	forboth(oid_item, tab->changedStatisticsOids,
 			def_item, tab->changedStatisticsDefs)
@@ -15582,6 +15614,29 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
 	 * The objects will get recreated during subsequent passes over the work
 	 * queue.
 	 */
+
+	/*
+	 * We have already deleted the dependent objects; now remove these objects
+	 * themselves to avoid deleting them twice.
+	 */
+	list_free(tab->changedConstraintOids);
+	list_free(tab->changedConstraintDefs);
+	list_free(tab->changedIndexOids);
+	list_free(tab->changedIndexDefs);
+	list_free(tab->changedStatisticsOids);
+	list_free(tab->changedStatisticsDefs);
+	list_free(tab->changedGenerationExprOids);
+	list_free(tab->changedGenerationExprDefs);
+
+	tab->changedConstraintOids = NIL;
+	tab->changedConstraintDefs = NIL;
+	tab->changedIndexOids = NIL;
+	tab->changedIndexDefs = NIL;
+	tab->changedStatisticsOids = NIL;
+	tab->changedStatisticsDefs = NIL;
+	tab->changedGenerationExprOids = NIL;
+	tab->changedGenerationExprDefs = NIL;
+
 }
 
 /*
@@ -15743,6 +15798,17 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
 					else
 						Assert(con->contype == CONSTR_NOTNULL);
 				}
+				else if (cmd->subtype == AT_SetExpression)
+				{
+					/*
+					 * The generation expression is rebuilt due to a data type
+					 * change, but AT_PASS_ALTER_TYPE occurs earlier than
+					 * AT_PASS_SET_EXPRESSION.  Therefore, we need to prepend
+					 * this command to the AT_PASS_SET_EXPRESSION pass.
+					 */
+					tab->subcmds[AT_PASS_SET_EXPRESSION] =
+						lcons(cmd, tab->subcmds[AT_PASS_SET_EXPRESSION]);
+				}
 				else
 					elog(ERROR, "unexpected statement subtype: %d",
 						 (int) cmd->subtype);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 050eef97a4c..6f75733620e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -25,6 +25,7 @@
 #include "access/table.h"
 #include "catalog/pg_aggregate.h"
 #include "catalog/pg_am.h"
+#include "catalog/pg_attrdef.h"
 #include "catalog/pg_authid.h"
 #include "catalog/pg_collation.h"
 #include "catalog/pg_constraint.h"
@@ -367,6 +368,7 @@ static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags,
 									  bool attrsOnly, bool missing_ok);
 static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
 										 int prettyFlags, bool missing_ok);
+static char *pg_get_generationdef_worker(Oid attrdefoid, bool fullCommand, int prettyFlags, bool missing_ok);
 static text *pg_get_expr_worker(text *expr, Oid relid, int prettyFlags);
 static int	print_function_arguments(StringInfo buf, HeapTuple proctup,
 									 bool print_table_args, bool print_defaults);
@@ -2185,6 +2187,112 @@ pg_get_constraintdef_command(Oid constraintId)
 	return pg_get_constraintdef_worker(constraintId, true, 0, false);
 }
 
+/*
+ * Internal version for use by ALTER TABLE.
+ * Returns a palloc'd C string; no pretty-printing.
+ */
+char *
+pg_get_generationdef(Oid attrdefoid)
+{
+	return pg_get_generationdef_worker(attrdefoid, true, 0, false);
+}
+
+/*
+ * if fullCommand is true, return
+ * "ALTER TABLE %s ALTER COLUMN %s SET EXPRESSION AS ( %s ) ",
+ * otherwise return the text of the genereration expression
+ */
+static char *
+pg_get_generationdef_worker(Oid attrdefoid, bool fullCommand,
+							int prettyFlags, bool missing_ok)
+{
+	HeapTuple	tup;
+	Form_pg_attrdef atdform;
+	StringInfoData buf;
+	SysScanDesc scandesc;
+	ScanKeyData scankey[1];
+	Relation	attrdef;
+	Datum		val;
+	text	   *result;
+	bool		isnull;
+
+	Snapshot	snapshot = RegisterSnapshot(GetTransactionSnapshot());
+	attrdef = table_open(AttrDefaultRelationId, AccessShareLock);
+
+	ScanKeyInit(&scankey[0],
+				Anum_pg_attrdef_oid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(attrdefoid));
+
+	scandesc = systable_beginscan(attrdef,
+								  AttrDefaultOidIndexId,
+								  true,
+								  snapshot,
+								  1,
+								  scankey);
+
+	/*
+	 * We later use the tuple with SysCacheGetAttr() as if we had obtained it
+	 * via SearchSysCache, which works fine.
+	 */
+	tup = systable_getnext(scandesc);
+
+	UnregisterSnapshot(snapshot);
+
+	if (!HeapTupleIsValid(tup))
+	{
+		if (missing_ok)
+		{
+			systable_endscan(scandesc);
+			table_close(attrdef, AccessShareLock);
+			return NULL;
+		}
+		elog(ERROR, "could not find tuple for attribute defaults %u", attrdefoid);
+	}
+
+	atdform = (Form_pg_attrdef) GETSTRUCT(tup);
+
+	initStringInfo(&buf);
+
+	if (fullCommand)
+	{
+		HeapTuple	tuple;
+		Form_pg_attribute attrtuple;
+
+		Assert(OidIsValid(atdform->adrelid));
+
+		tuple = SearchSysCacheAttNum(atdform->adrelid, atdform->adnum);
+		if (!HeapTupleIsValid(tuple))
+			elog(ERROR, "column number %d of relation %u does not exist",
+						atdform->adnum, atdform->adrelid);
+
+		attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
+		appendStringInfo(&buf, "ALTER TABLE %s ALTER COLUMN %s SET EXPRESSION AS ( ",
+						 generate_qualified_relation_name(atdform->adrelid),
+						 quote_identifier(NameStr(attrtuple->attname)));
+
+		ReleaseSysCache(tuple);
+	}
+
+	val = fastgetattr(tup,
+					  Anum_pg_attrdef_adbin,
+					  RelationGetDescr(attrdef), &isnull);
+	if (isnull)
+		elog(ERROR, "null adbin for rel %u", atdform->adrelid);
+
+	result = pg_get_expr_worker(DatumGetTextPP(val), atdform->adrelid, 0);
+
+	appendStringInfo(&buf, "%s", text_to_cstring(result));
+
+	if (fullCommand)
+		appendStringInfoChar(&buf, ')');
+
+	systable_endscan(scandesc);
+	table_close(attrdef, AccessShareLock);
+
+	return buf.data;
+}
+
 /*
  * As of 9.4, we now use an MVCC snapshot for this.
  */
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..a1d629dccfd 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -53,5 +53,6 @@ extern char *generate_opclass_name(Oid opclass);
 extern char *get_range_partbound_string(List *bound_datums);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
+extern char *pg_get_generationdef(Oid attrdefoid);
 
 #endif							/* RULEUTILS_H */
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..1f43f90fac1 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1120,6 +1120,41 @@ SELECT * FROM gtest25 ORDER BY a;
 Indexes:
     "gtest25_pkey" PRIMARY KEY, btree (a)
 
+CREATE STATISTICS gtest25_stat ON ((a + 1) is not null or (b + 1) is not null) FROM gtest25;
+CREATE STATISTICS gtest25_1_stat ON ((b + 1) is not null) FROM gtest25;
+CREATE INDEX gtest25_a_b_idx ON gtest25((a+b));
+CREATE INDEX gtest25_b_idx ON gtest25((b));
+ALTER TABLE gtest25 ALTER COLUMN a SET DATA TYPE BIGINT USING (a);
+--change type and change generation expression together
+ALTER TABLE gtest25
+  ALTER COLUMN a SET DATA TYPE numeric USING (a + 1),
+  ALTER COLUMN b SET DATA TYPE BIGINT,
+  ALTER COLUMN b SET EXPRESSION AS (a * 4);
+\d gtest25
+                                 Table "generated_stored_tests.gtest25"
+ Column |       Type       | Collation | Nullable |                       Default                        
+--------+------------------+-----------+----------+------------------------------------------------------
+ a      | numeric          |           | not null | 
+ b      | bigint           |           |          | generated always as ((a * 4::numeric)) stored
+ c      | integer          |           |          | 42
+ x      | integer          |           |          | generated always as (c * 4) stored
+ d      | double precision |           |          | 101
+ y      | double precision |           |          | generated always as (d * 4::double precision) stored
+Indexes:
+    "gtest25_pkey" PRIMARY KEY, btree (a)
+    "gtest25_a_b_idx" btree ((a + b::numeric))
+    "gtest25_b_idx" btree (b)
+Statistics objects:
+    "generated_stored_tests.gtest25_1_stat" ON ((b + 1) IS NOT NULL) FROM gtest25
+    "generated_stored_tests.gtest25_stat" ON ((a + 1::numeric) IS NOT NULL OR (b + 1) IS NOT NULL) FROM gtest25
+
+SELECT * FROM gtest25 ORDER BY a;
+ a | b  | c  |  x  |  d  |  y  
+---+----+----+-----+-----+-----
+ 4 | 16 | 42 | 168 | 101 | 404
+ 5 | 20 | 42 | 168 | 101 | 404
+(2 rows)
+
 -- ALTER TABLE ... ALTER COLUMN
 CREATE TABLE gtest27 (
     a int,
@@ -1128,14 +1163,16 @@ CREATE TABLE gtest27 (
 );
 INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
 ALTER TABLE gtest27 ALTER COLUMN a TYPE text;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+ERROR:  operator does not exist: text + integer
+DETAIL:  No operator of that name accepts the given argument types.
+HINT:  You might need to add explicit type casts.
+ALTER TABLE gtest27 ALTER COLUMN a TYPE int8;  -- ok
 ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
 \d gtest27
                         Table "generated_stored_tests.gtest27"
  Column |  Type   | Collation | Nullable |                  Default                   
 --------+---------+-----------+----------+--------------------------------------------
- a      | integer |           |          | 
+ a      | bigint  |           |          | 
  b      | integer |           |          | 
  x      | numeric |           |          | generated always as (((a + b) * 2)) stored
 
@@ -1171,16 +1208,14 @@ ALTER TABLE gtest27
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+  ALTER COLUMN b TYPE float8;  -- ok
 \d gtest27
-                      Table "generated_stored_tests.gtest27"
- Column |  Type  | Collation | Nullable |                 Default                  
---------+--------+-----------+----------+------------------------------------------
- a      | bigint |           |          | 
- b      | bigint |           |          | 
- x      | bigint |           |          | generated always as ((a + b) * 2) stored
+                                     Table "generated_stored_tests.gtest27"
+ Column |       Type       | Collation | Nullable |                           Default                            
+--------+------------------+-----------+----------+--------------------------------------------------------------
+ a      | double precision |           |          | 
+ b      | double precision |           |          | 
+ x      | bigint           |           |          | generated always as (((a + b) * 2::double precision)) stored
 
 SELECT * FROM gtest27;
  a | b  | x  
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index c861bd36c5a..c748d4f85e9 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1082,6 +1082,32 @@ SELECT * FROM gtest25 ORDER BY a;
 Indexes:
     "gtest25_pkey" PRIMARY KEY, btree (a)
 
+ALTER TABLE gtest25 ALTER COLUMN a SET DATA TYPE BIGINT USING (a);
+--change type and change generation expression together
+ALTER TABLE gtest25
+  ALTER COLUMN a SET DATA TYPE numeric USING (a + 1),
+  ALTER COLUMN b SET DATA TYPE BIGINT,
+  ALTER COLUMN b SET EXPRESSION AS (a * 4);
+\d gtest25
+                             Table "generated_virtual_tests.gtest25"
+ Column |       Type       | Collation | Nullable |                    Default                    
+--------+------------------+-----------+----------+-----------------------------------------------
+ a      | numeric          |           | not null | 
+ b      | bigint           |           |          | generated always as ((a * 4::numeric))
+ c      | integer          |           |          | 42
+ x      | integer          |           |          | generated always as (c * 4)
+ d      | double precision |           |          | 101
+ y      | double precision |           |          | generated always as (d * 4::double precision)
+Indexes:
+    "gtest25_pkey" PRIMARY KEY, btree (a)
+
+SELECT * FROM gtest25 ORDER BY a;
+ a | b  | c  |  x  |  d  |  y  
+---+----+----+-----+-----+-----
+ 4 | 16 | 42 | 168 | 101 | 404
+ 5 | 20 | 42 | 168 | 101 | 404
+(2 rows)
+
 -- ALTER TABLE ... ALTER COLUMN
 CREATE TABLE gtest27 (
     a int,
@@ -1090,14 +1116,16 @@ CREATE TABLE gtest27 (
 );
 INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
 ALTER TABLE gtest27 ALTER COLUMN a TYPE text;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+ERROR:  operator does not exist: text + integer
+DETAIL:  No operator of that name accepts the given argument types.
+HINT:  You might need to add explicit type casts.
+ALTER TABLE gtest27 ALTER COLUMN a TYPE int8;  -- ok
 ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
 \d gtest27
                     Table "generated_virtual_tests.gtest27"
  Column |  Type   | Collation | Nullable |               Default               
 --------+---------+-----------+----------+-------------------------------------
- a      | integer |           |          | 
+ a      | bigint  |           |          | 
  b      | integer |           |          | 
  x      | numeric |           |          | generated always as (((a + b) * 2))
 
@@ -1141,16 +1169,14 @@ ALTER TABLE gtest27
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+  ALTER COLUMN b TYPE float8;  -- ok
 \d gtest27
-                  Table "generated_virtual_tests.gtest27"
- Column |  Type  | Collation | Nullable |              Default              
---------+--------+-----------+----------+-----------------------------------
- a      | bigint |           |          | 
- b      | bigint |           |          | 
- x      | bigint |           |          | generated always as ((a + b) * 2)
+                                 Table "generated_virtual_tests.gtest27"
+ Column |       Type       | Collation | Nullable |                        Default                        
+--------+------------------+-----------+----------+-------------------------------------------------------
+ a      | double precision |           |          | 
+ b      | double precision |           |          | 
+ x      | bigint           |           |          | generated always as (((a + b) * 2::double precision))
 
 SELECT * FROM gtest27;
  a | b  | x  
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index f56fde8d4e5..77a51edeeef 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -517,6 +517,21 @@ ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
 SELECT * FROM gtest25 ORDER BY a;
 \d gtest25
 
+CREATE STATISTICS gtest25_stat ON ((a + 1) is not null or (b + 1) is not null) FROM gtest25;
+CREATE STATISTICS gtest25_1_stat ON ((b + 1) is not null) FROM gtest25;
+CREATE INDEX gtest25_a_b_idx ON gtest25((a+b));
+CREATE INDEX gtest25_b_idx ON gtest25((b));
+
+ALTER TABLE gtest25 ALTER COLUMN a SET DATA TYPE BIGINT USING (a);
+--change type and change generation expression together
+ALTER TABLE gtest25
+  ALTER COLUMN a SET DATA TYPE numeric USING (a + 1),
+  ALTER COLUMN b SET DATA TYPE BIGINT,
+  ALTER COLUMN b SET EXPRESSION AS (a * 4);
+\d gtest25
+
+SELECT * FROM gtest25 ORDER BY a;
+
 -- ALTER TABLE ... ALTER COLUMN
 CREATE TABLE gtest27 (
     a int,
@@ -525,6 +540,7 @@ CREATE TABLE gtest27 (
 );
 INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
 ALTER TABLE gtest27 ALTER COLUMN a TYPE text;  -- error
+ALTER TABLE gtest27 ALTER COLUMN a TYPE int8;  -- ok
 ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
 \d gtest27
 SELECT * FROM gtest27;
@@ -540,7 +556,7 @@ ALTER TABLE gtest27
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
+  ALTER COLUMN b TYPE float8;  -- ok
 \d gtest27
 SELECT * FROM gtest27;
 
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index adfe88d74ae..b324a12bf08 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -560,6 +560,16 @@ ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
 SELECT * FROM gtest25 ORDER BY a;
 \d gtest25
 
+ALTER TABLE gtest25 ALTER COLUMN a SET DATA TYPE BIGINT USING (a);
+--change type and change generation expression together
+ALTER TABLE gtest25
+  ALTER COLUMN a SET DATA TYPE numeric USING (a + 1),
+  ALTER COLUMN b SET DATA TYPE BIGINT,
+  ALTER COLUMN b SET EXPRESSION AS (a * 4);
+
+\d gtest25
+SELECT * FROM gtest25 ORDER BY a;
+
 -- ALTER TABLE ... ALTER COLUMN
 CREATE TABLE gtest27 (
     a int,
@@ -568,6 +578,7 @@ CREATE TABLE gtest27 (
 );
 INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
 ALTER TABLE gtest27 ALTER COLUMN a TYPE text;  -- error
+ALTER TABLE gtest27 ALTER COLUMN a TYPE int8;  -- ok
 ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
 \d gtest27
 SELECT * FROM gtest27;
@@ -591,7 +602,7 @@ ALTER TABLE gtest27
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
+  ALTER COLUMN b TYPE float8;  -- ok
 \d gtest27
 SELECT * FROM gtest27;
 
-- 
2.34.1

Chao Li

li.evan.chao@gmail.com

3 months ago

In reply to: jian he (#2)

1 attachment(s)

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

Hi Jian,

Thanks for looking into this problem.

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

/messages/by-id/CACJufxHZsgn3zM5g-x7YmtFGzNDnRwR07S+GYfiUs+tZ45MDDw@mail.gmail.com

I think we can keep the discussion in your thread. Actually, I got this
idea from your discussion.

But for this topic, I don't want to go that far in the first place, because
completely allowing that might require more discussion.

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);
```

Best regards,
Chao Li (Evan)
---------------------
HighGo Software Co., Ltd.
https://www.highgo.com/

Attachments:

v1-0001-Alow-ALTER-COLUMN-TYPE-when-dependent-generated-c.patchapplication/octet-stream; name=v1-0001-Alow-ALTER-COLUMN-TYPE-when-dependent-generated-c.patchDownload

From bed3313b134158e6fa876dd59e7e16e1f703db97 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <lic@highgo.com>
Date: Tue, 21 Oct 2025 14:28:48 +0800
Subject: [PATCH v1] Alow ALTER COLUMN TYPE when dependent generated column has
 SET EXPRESSION
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Previously, altering the type of a column that is referenced by a
generated column would fail, because changing the base column’s
type could invalidate the generated column’s expression.

With this patch, if the ALTER TABLE command also provides a SET
EXPRESSION clause for the dependent generated column, the type
change is allowed. This ensures that the generated column can be
safely updated to reflect the new base column type within the
same statement.

Example:

-- 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);

Author: Chao Li <lic@highgo.com>
Discussion: https://postgr.es/m/A61F14F6-339D-46EF-9FC1-A8438FE3BE86@gmail.com
---
 src/backend/commands/tablecmds.c              | 51 +++++++++++++++----
 .../regress/expected/generated_stored.out     | 37 ++++++++++++--
 .../regress/expected/generated_virtual.out    | 37 ++++++++++++--
 src/test/regress/sql/generated_stored.sql     | 13 +++++
 src/test/regress/sql/generated_virtual.sql    | 13 +++++
 5 files changed, 130 insertions(+), 21 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5fd8b51312c..95123e0fdbb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -739,7 +739,7 @@ static List *GetParentedForeignKeyRefs(Relation partition);
 static void ATDetachCheckNoForeignKeyRefs(Relation partition);
 static char GetAttributeCompression(Oid atttypid, const char *compression);
 static char GetAttributeStorage(Oid atttypid, const char *storagemode);
-
+static bool ColumnHasSetExpression(const AlteredTableInfo *tab, const char *colname);
 
 /* ----------------------------------------------------------------
  *		DefineRelation
@@ -15172,18 +15172,27 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
 						 * 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.
+						 * by SQL standard, so just punt for now. However, if
+						 * the generated column has a SET EXPRESSION, then we
+						 * can allow the column type change.
 						 */
 						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))));
+						{
+							const char *genColName;
+
+							Assert(rel->rd_rel->oid == col.objectId);
+							genColName = get_attname(col.objectId,
+													 col.objectSubId,
+													 false);
+
+							if (!ColumnHasSetExpression(tab, genColName))
+								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,
+												   genColName)));
+						}
 					}
 					break;
 				}
@@ -22012,3 +22021,23 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
 
 	return cstorage;
 }
+
+/*
+ * Check if column has set expression in the ALTER TABLE command.
+ */
+static bool
+ColumnHasSetExpression(const AlteredTableInfo *tab, const char *colname)
+{
+	ListCell   *lc;
+
+	foreach(lc, tab->subcmds[AT_PASS_SET_EXPRESSION])
+	{
+		AlterTableCmd *cmd = (AlterTableCmd *) lfirst(lc);
+
+		if (strcmp(cmd->name, colname) == 0)
+		{
+			return true;
+		}
+	}
+	return false;
+}
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..33751462eca 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1168,12 +1168,12 @@ ALTER TABLE gtest27
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2) stored
 
--- Ideally you could just do this, but not today (and should x change type?):
+-- Also possible this way:
 ALTER TABLE gtest27
-  ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
 \d gtest27
                       Table "generated_stored_tests.gtest27"
  Column |  Type  | Collation | Nullable |                 Default                  
@@ -1182,6 +1182,33 @@ DETAIL:  Column "a" is used by generated column "x".
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2) stored
 
+-- 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
+
+-- Ideally you could just do this, but not today (and should x change type?):
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE float8,
+  ALTER COLUMN b TYPE float8;  -- error
+ERROR:  cannot alter type of a column used by a generated column
+DETAIL:  Column "a" is used by generated column "x".
+\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
+
 SELECT * FROM gtest27;
  a | b  | x  
 ---+----+----
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index c861bd36c5a..95f0bc17897 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1138,12 +1138,12 @@ ALTER TABLE gtest27
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2)
 
--- Ideally you could just do this, but not today (and should x change type?):
+-- Also possible this way:
 ALTER TABLE gtest27
-  ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
 \d gtest27
                   Table "generated_virtual_tests.gtest27"
  Column |  Type  | Collation | Nullable |              Default              
@@ -1152,6 +1152,33 @@ DETAIL:  Column "a" is used by generated column "x".
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2)
 
+-- 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_virtual_tests.gtest27"
+ Column |       Type       | Collation | Nullable |                  Default                   
+--------+------------------+-----------+----------+--------------------------------------------
+ a      | double precision |           |          | 
+ b      | bigint           |           |          | 
+ x      | bigint           |           |          | generated always as ((a::integer + b) * 2)
+
+-- Ideally you could just do this, but not today (and should x change type?):
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE float8,
+  ALTER COLUMN b TYPE float8;  -- error
+ERROR:  cannot alter type of a column used by a generated column
+DETAIL:  Column "a" is used by generated column "x".
+\d gtest27
+                            Table "generated_virtual_tests.gtest27"
+ Column |       Type       | Collation | Nullable |                  Default                   
+--------+------------------+-----------+----------+--------------------------------------------
+ a      | double precision |           |          | 
+ b      | bigint           |           |          | 
+ x      | bigint           |           |          | generated always as ((a::integer + b) * 2)
+
 SELECT * FROM gtest27;
  a | b  | x  
 ---+----+----
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index f56fde8d4e5..4e2d0f9178d 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -537,6 +537,19 @@ ALTER TABLE gtest27
   ALTER COLUMN b TYPE bigint,
   ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
 \d gtest27
+-- Also possible this way:
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
+\d gtest27
+-- 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
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index adfe88d74ae..c04b48ec86f 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -588,6 +588,19 @@ ALTER TABLE gtest27
   ALTER COLUMN b TYPE bigint,
   ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL;
 \d gtest27
+-- Also possible this way:
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
+\d gtest27
+-- 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
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
-- 
2.39.5 (Apple Git-154)

jian he

jian.universality@gmail.com

3 months ago

In reply to: Chao Li (#3)

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 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

which conflicts with your changes.

Chao Li

li.evan.chao@gmail.com

3 months ago

In reply to: jian he (#4)

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

On Oct 21, 2025, at 15:30, jian he <jian.universality@gmail.com> wrote:

+-- 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

I don’t get what is wrong? Actually “make check” passed from my side:

```
% make check
…
1..230
# All 230 tests passed.
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

jian he

jian.universality@gmail.com

3 months ago

In reply to: Chao Li (#5)

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

On Tue, Oct 21, 2025 at 3:56 PM Chao Li <li.evan.chao@gmail.com> wrote:

On Oct 21, 2025, at 15:30, jian he <jian.universality@gmail.com> wrote:

+-- 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

I don’t get what is wrong? Actually “make check” passed from my side:

Sorry for the noise, I misunderstood something.
I think I understand what your patch does now.

I’ll polish my patch and submit it in a new thread.

Chao Li

li.evan.chao@gmail.com

3 months ago

In reply to: Chao Li (#5)

1 attachment(s)

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

```
% make check
…
1..230
# All 230 tests passed.
```

Rebased v2 attached.

Best regards,
Chao Li (Evan)
---------------------
HighGo Software Co., Ltd.
https://www.highgo.com/

Attachments:

v2-0001-Alow-ALTER-COLUMN-TYPE-when-dependent-generated-c.patchapplication/octet-stream; name=v2-0001-Alow-ALTER-COLUMN-TYPE-when-dependent-generated-c.patchDownload

From 08023c65c80527f119ca12368f43822f0cdc3b19 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <lic@highgo.com>
Date: Tue, 21 Oct 2025 14:28:48 +0800
Subject: [PATCH v2] Alow ALTER COLUMN TYPE when dependent generated column has
 SET EXPRESSION
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Previously, altering the type of a column that is referenced by a
generated column would fail, because changing the base column’s
type could invalidate the generated column’s expression.

With this patch, if the ALTER TABLE command also provides a SET
EXPRESSION clause for the dependent generated column, the type
change is allowed. This ensures that the generated column can be
safely updated to reflect the new base column type within the
same statement.

Example:

-- 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);

Author: Chao Li <lic@highgo.com>
Discussion: https://postgr.es/m/A61F14F6-339D-46EF-9FC1-A8438FE3BE86@gmail.com
---
 src/backend/commands/tablecmds.c              | 51 +++++++++++++++----
 .../regress/expected/generated_stored.out     | 37 ++++++++++++--
 .../regress/expected/generated_virtual.out    | 37 ++++++++++++--
 src/test/regress/sql/generated_stored.sql     | 13 +++++
 src/test/regress/sql/generated_virtual.sql    | 13 +++++
 5 files changed, 130 insertions(+), 21 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5fd8b51312c..95123e0fdbb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -739,7 +739,7 @@ static List *GetParentedForeignKeyRefs(Relation partition);
 static void ATDetachCheckNoForeignKeyRefs(Relation partition);
 static char GetAttributeCompression(Oid atttypid, const char *compression);
 static char GetAttributeStorage(Oid atttypid, const char *storagemode);
-
+static bool ColumnHasSetExpression(const AlteredTableInfo *tab, const char *colname);
 
 /* ----------------------------------------------------------------
  *		DefineRelation
@@ -15172,18 +15172,27 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
 						 * 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.
+						 * by SQL standard, so just punt for now. However, if
+						 * the generated column has a SET EXPRESSION, then we
+						 * can allow the column type change.
 						 */
 						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))));
+						{
+							const char *genColName;
+
+							Assert(rel->rd_rel->oid == col.objectId);
+							genColName = get_attname(col.objectId,
+													 col.objectSubId,
+													 false);
+
+							if (!ColumnHasSetExpression(tab, genColName))
+								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,
+												   genColName)));
+						}
 					}
 					break;
 				}
@@ -22012,3 +22021,23 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
 
 	return cstorage;
 }
+
+/*
+ * Check if column has set expression in the ALTER TABLE command.
+ */
+static bool
+ColumnHasSetExpression(const AlteredTableInfo *tab, const char *colname)
+{
+	ListCell   *lc;
+
+	foreach(lc, tab->subcmds[AT_PASS_SET_EXPRESSION])
+	{
+		AlterTableCmd *cmd = (AlterTableCmd *) lfirst(lc);
+
+		if (strcmp(cmd->name, colname) == 0)
+		{
+			return true;
+		}
+	}
+	return false;
+}
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..33751462eca 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1168,12 +1168,12 @@ ALTER TABLE gtest27
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2) stored
 
--- Ideally you could just do this, but not today (and should x change type?):
+-- Also possible this way:
 ALTER TABLE gtest27
-  ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
 \d gtest27
                       Table "generated_stored_tests.gtest27"
  Column |  Type  | Collation | Nullable |                 Default                  
@@ -1182,6 +1182,33 @@ DETAIL:  Column "a" is used by generated column "x".
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2) stored
 
+-- 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
+
+-- Ideally you could just do this, but not today (and should x change type?):
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE float8,
+  ALTER COLUMN b TYPE float8;  -- error
+ERROR:  cannot alter type of a column used by a generated column
+DETAIL:  Column "a" is used by generated column "x".
+\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
+
 SELECT * FROM gtest27;
  a | b  | x  
 ---+----+----
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index c861bd36c5a..95f0bc17897 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1138,12 +1138,12 @@ ALTER TABLE gtest27
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2)
 
--- Ideally you could just do this, but not today (and should x change type?):
+-- Also possible this way:
 ALTER TABLE gtest27
-  ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
 \d gtest27
                   Table "generated_virtual_tests.gtest27"
  Column |  Type  | Collation | Nullable |              Default              
@@ -1152,6 +1152,33 @@ DETAIL:  Column "a" is used by generated column "x".
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2)
 
+-- 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_virtual_tests.gtest27"
+ Column |       Type       | Collation | Nullable |                  Default                   
+--------+------------------+-----------+----------+--------------------------------------------
+ a      | double precision |           |          | 
+ b      | bigint           |           |          | 
+ x      | bigint           |           |          | generated always as ((a::integer + b) * 2)
+
+-- Ideally you could just do this, but not today (and should x change type?):
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE float8,
+  ALTER COLUMN b TYPE float8;  -- error
+ERROR:  cannot alter type of a column used by a generated column
+DETAIL:  Column "a" is used by generated column "x".
+\d gtest27
+                            Table "generated_virtual_tests.gtest27"
+ Column |       Type       | Collation | Nullable |                  Default                   
+--------+------------------+-----------+----------+--------------------------------------------
+ a      | double precision |           |          | 
+ b      | bigint           |           |          | 
+ x      | bigint           |           |          | generated always as ((a::integer + b) * 2)
+
 SELECT * FROM gtest27;
  a | b  | x  
 ---+----+----
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index f56fde8d4e5..4e2d0f9178d 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -537,6 +537,19 @@ ALTER TABLE gtest27
   ALTER COLUMN b TYPE bigint,
   ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
 \d gtest27
+-- Also possible this way:
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
+\d gtest27
+-- 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
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index adfe88d74ae..c04b48ec86f 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -588,6 +588,19 @@ ALTER TABLE gtest27
   ALTER COLUMN b TYPE bigint,
   ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL;
 \d gtest27
+-- Also possible this way:
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
+\d gtest27
+-- 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
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
-- 
2.39.5 (Apple Git-154)

Chao Li

li.evan.chao@gmail.com

about 1 month ago

In reply to: Chao Li (#7)

1 attachment(s)

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

On Oct 22, 2025, at 16:57, Chao Li <li.evan.chao@gmail.com> wrote:

```
% make check
…
1..230
# All 230 tests passed.
```

Rebased v2 attached.

Bump. And rebased to v3.

When you have a column that is used by a generated column, if you need to alter type of the column:

```
-- 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);
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Attachments:

v3-0001-Alow-ALTER-COLUMN-TYPE-when-dependent-generated-c.patchapplication/octet-stream; name=v3-0001-Alow-ALTER-COLUMN-TYPE-when-dependent-generated-c.patch; x-unix-mode=0644Download

From 6e377eaadf2be9599f82e8e0c426eb800218b375 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <lic@highgo.com>
Date: Tue, 21 Oct 2025 14:28:48 +0800
Subject: [PATCH v3] Alow ALTER COLUMN TYPE when dependent generated column has
 SET EXPRESSION
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Previously, altering the type of a column that is referenced by a
generated column would fail, because changing the base column’s
type could invalidate the generated column’s expression.

With this patch, if the ALTER TABLE command also provides a SET
EXPRESSION clause for the dependent generated column, the type
change is allowed. This ensures that the generated column can be
safely updated to reflect the new base column type within the
same statement.

Example:

-- 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);

Author: Chao Li <lic@highgo.com>
Discussion: https://postgr.es/m/A61F14F6-339D-46EF-9FC1-A8438FE3BE86@gmail.com
---
 src/backend/commands/tablecmds.c              | 51 +++++++++++++++----
 .../regress/expected/generated_stored.out     | 37 ++++++++++++--
 .../regress/expected/generated_virtual.out    | 37 ++++++++++++--
 src/test/regress/sql/generated_stored.sql     | 13 +++++
 src/test/regress/sql/generated_virtual.sql    | 13 +++++
 5 files changed, 130 insertions(+), 21 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 07e5b95782e..13513281379 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -739,7 +739,7 @@ static List *GetParentedForeignKeyRefs(Relation partition);
 static void ATDetachCheckNoForeignKeyRefs(Relation partition);
 static char GetAttributeCompression(Oid atttypid, const char *compression);
 static char GetAttributeStorage(Oid atttypid, const char *storagemode);
-
+static bool ColumnHasSetExpression(const AlteredTableInfo *tab, const char *colname);
 
 /* ----------------------------------------------------------------
  *		DefineRelation
@@ -15197,18 +15197,27 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
 						 * 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.
+						 * by SQL standard, so just punt for now. However, if
+						 * the generated column has a SET EXPRESSION, then we
+						 * can allow the column type change.
 						 */
 						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))));
+						{
+							const char *genColName;
+
+							Assert(rel->rd_rel->oid == col.objectId);
+							genColName = get_attname(col.objectId,
+													 col.objectSubId,
+													 false);
+
+							if (!ColumnHasSetExpression(tab, genColName))
+								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,
+												   genColName)));
+						}
 					}
 					break;
 				}
@@ -22047,3 +22056,23 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
 
 	return cstorage;
 }
+
+/*
+ * Check if column has set expression in the ALTER TABLE command.
+ */
+static bool
+ColumnHasSetExpression(const AlteredTableInfo *tab, const char *colname)
+{
+	ListCell   *lc;
+
+	foreach(lc, tab->subcmds[AT_PASS_SET_EXPRESSION])
+	{
+		AlterTableCmd *cmd = (AlterTableCmd *) lfirst(lc);
+
+		if (strcmp(cmd->name, colname) == 0)
+		{
+			return true;
+		}
+	}
+	return false;
+}
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 8b7a71d8f0c..11b848cb035 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1189,12 +1189,12 @@ ALTER TABLE gtest27
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2) stored
 
--- Ideally you could just do this, but not today (and should x change type?):
+-- Also possible this way:
 ALTER TABLE gtest27
-  ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
 \d gtest27
                       Table "generated_stored_tests.gtest27"
  Column |  Type  | Collation | Nullable |                 Default                  
@@ -1203,6 +1203,33 @@ DETAIL:  Column "a" is used by generated column "x".
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2) stored
 
+-- 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
+
+-- Ideally you could just do this, but not today (and should x change type?):
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE float8,
+  ALTER COLUMN b TYPE float8;  -- error
+ERROR:  cannot alter type of a column used by a generated column
+DETAIL:  Column "a" is used by generated column "x".
+\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
+
 SELECT * FROM gtest27;
  a | b  | x  
 ---+----+----
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index dde325e46c6..e01f92d4dcd 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1159,12 +1159,12 @@ ALTER TABLE gtest27
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2)
 
--- Ideally you could just do this, but not today (and should x change type?):
+-- Also possible this way:
 ALTER TABLE gtest27
-  ALTER COLUMN a TYPE float8,
-  ALTER COLUMN b TYPE float8;  -- error
-ERROR:  cannot alter type of a column used by a generated column
-DETAIL:  Column "a" is used by generated column "x".
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
 \d gtest27
                   Table "generated_virtual_tests.gtest27"
  Column |  Type  | Collation | Nullable |              Default              
@@ -1173,6 +1173,33 @@ DETAIL:  Column "a" is used by generated column "x".
  b      | bigint |           |          | 
  x      | bigint |           |          | generated always as ((a + b) * 2)
 
+-- 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_virtual_tests.gtest27"
+ Column |       Type       | Collation | Nullable |                  Default                   
+--------+------------------+-----------+----------+--------------------------------------------
+ a      | double precision |           |          | 
+ b      | bigint           |           |          | 
+ x      | bigint           |           |          | generated always as ((a::integer + b) * 2)
+
+-- Ideally you could just do this, but not today (and should x change type?):
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE float8,
+  ALTER COLUMN b TYPE float8;  -- error
+ERROR:  cannot alter type of a column used by a generated column
+DETAIL:  Column "a" is used by generated column "x".
+\d gtest27
+                            Table "generated_virtual_tests.gtest27"
+ Column |       Type       | Collation | Nullable |                  Default                   
+--------+------------------+-----------+----------+--------------------------------------------
+ a      | double precision |           |          | 
+ b      | bigint           |           |          | 
+ x      | bigint           |           |          | generated always as ((a::integer + b) * 2)
+
 SELECT * FROM gtest27;
  a | b  | x  
 ---+----+----
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index 2001a47bcc6..2f288cd50be 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -544,6 +544,19 @@ ALTER TABLE gtest27
   ALTER COLUMN b TYPE bigint,
   ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
 \d gtest27
+-- Also possible this way:
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
+\d gtest27
+-- 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
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 2911439776c..59c21df82a5 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -595,6 +595,19 @@ ALTER TABLE gtest27
   ALTER COLUMN b TYPE bigint,
   ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL;
 \d gtest27
+-- Also possible this way:
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE bigint,
+  ALTER COLUMN b TYPE bigint,
+  ALTER COLUMN x TYPE bigint,
+  ALTER COLUMN x SET EXPRESSION AS ((a + b) * 2);
+\d gtest27
+-- 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
 -- Ideally you could just do this, but not today (and should x change type?):
 ALTER TABLE gtest27
   ALTER COLUMN a TYPE float8,
-- 
2.39.5 (Apple Git-154)