From: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Ilya Kosmodemiansky <ik@dataegret.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: GENERATE AS
Date: Thu, 22 Jun 2023 22:00:47 +0000
Message-ID: <754d7cd591b74c8585f6efb0f4aef81a@express-scripts.com> (raw)
In-Reply-To: <4108564.1687468505@sss.pgh.pa.us>
References: <7c48174f8ce449af8f3e875bdeca7150@express-scripts.com>
<D598E4B1-1B40-4B55-89A2-9296CDD2985D@dataegret.com>
<CAKFQuwbJcON2=Ky9rDNJdUGa8iuu6nYnPnRa_jr85iRkCokKww@mail.gmail.com>
<4108564.1687468505@sss.pgh.pa.us>
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@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. 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Matthew.Wetmore@express-scripts.com, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, ik@dataegret.com, pgsql-admin@lists.postgresql.org
Subject: Re: GENERATE AS
In-Reply-To: <754d7cd591b74c8585f6efb0f4aef81a@express-scripts.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox