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 was very 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 time that 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 seems simplified, 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(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Thursday, June 22, 2023 2:15 PM
To: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Ilya Kosmodemiansky <ik(at)dataegret(dot)com>; Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: [EXTERNAL] Re: GENERATE AS

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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. Depending on 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