Thread: altering a column to to make it generated
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 ...
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! :-) ^
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:
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