altering a column to to make it generated

On Mon, Apr 22, 2024 at 12:42 PM Celia McInnis <celia.mcinnis@gmail.com>
wrote:

Can I alter a table column to now make it generated? I tried this
unsuccessfully:

I looked at all of the "alter table ... alter column" commands listed here:

https://www.postgresql.org/docs/current/sql-altertable.html

And none seem to involve the generated expression column. So the answer is
no.

David J.

On 4/22/24 12:42, Celia McInnis wrote:

Can I alter a table column to now make it generated? I tried this
unsuccessfully:

create temp table tmp1 as select 15::numeric(6,1) as
distance,'24:30'::interval,0::numeric(7,3) as avgspd;
alter table tmp1 alter column avgspd type numeric(7,3) generated always
as ((3600.*distance)/EXTRACT (EPOCH FROM rdrtime::interval)) STORED;
ERROR:  syntax error at or near "generated"
LINE 1: ... table tmp1 alter column avgspd type numeric(7,3) generated ...

I think that at least I have the right bracketing this time! :-)

See David Johnston's comment. I keep on wanting to believe that ALTER
TABLE supports GENERATED expressions, when it only supports GENERATED
IDENTITY:)

--
Adrian Klaver
adrian.klaver@aklaver.com

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Mon, Apr 22, 2024 at 12:42 PM Celia McInnis <celia.mcinnis@gmail.com>
wrote:

Can I alter a table column to now make it generated? I tried this
unsuccessfully:

I looked at all of the "alter table ... alter column" commands listed here:
https://www.postgresql.org/docs/current/sql-altertable.html
And none seem to involve the generated expression column. So the answer is
no.

I think what you'd need to do is drop the existing column
and then add a generated column.

regards, tom lane