Re: altering a column to to make it generated

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, Apr 22, 2024 at 12:42 PM Celia McInnis 
> 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

Re: altering a column to to make it generated

[Adrian Klaver]

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.kla...@aklaver.com

Re: altering a column to to make it generated

[David G. Johnston]

On Mon, Apr 22, 2024 at 12:42 PM Celia McInnis 
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.

altering a column to to make it generated

[Celia McInnis]

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! :-)
 ^