GENERATE AS

I got his working.

I understand everyone's great suggestions on functions and views, that's how I would have done this, but my requirement
wasvery specific to my org. 

This stemmed from a re-architecture  from MSSQL, and had to stay as is for MainFrame (I tried to get to change)

Basically, you just can't use the time functions(), in the GENERATE you have to create a separate column with default
timethat in my case (isn't used by the other system, but gets defaulted on INSERT.) 

Wonky, yes, but it will work for my specific need.  (I have whitewashed the specific need for security, so if this
seemssimplified, it is.) 

Thanks for all the replies!


ALTER TABLE matt
    add column matt_time timestamp without time zone default current_timestamp

ALTER TABLE matt
    add column matt_time2 timestamp without time zone default current_timestamp

ALTER TABLE matt
    ADD column matt_minute NUMERIC GENERATED ALWAYS AS ((EXTRACT(MINUTE FROM matt_time))::numeric -(extract(MINUTE FROM
matt_time2))::numeric)stored 

ALTER TABLE matt
    ADD column matt_hour NUMERIC GENERATED ALWAYS AS ((EXTRACT(HOUR FROM matt_time))::numeric -(extract(HOUR FROM
matt_time2))::numeric)stored 

select * from auto_auth.matt

"matt_time"            "matt_time2"                    "matt_hour"    "matt_minute"
"2023-06-22 17:30:53.33997"    "2023-06-22 17:36:27.356768"            0        -6




-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, June 22, 2023 2:15 PM
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Ilya Kosmodemiansky <ik@dataegret.com>; Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>;
pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: GENERATE AS

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The problem here is not timezone related - it is more fundamental in
> that the stored data does not change but the expression, as time
> progresses, is going to give different values and thus make what is stored incorrect.

Right --- current_timestamp in a generation expression makes no sense.
(There's also an issue about whether the surrounding calculation is timezone-dependent, but that could be fixed up.
Dependingon current time cannot be.) 

You could create a view in which this value is an extra computed column, but you can't do it as a stored generated
column.

            regards, tom lane