pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedGENERATE AS
9+ messages / 6 participants
[nested] [flat]
* GENERATE AS
@ 2023-06-22 20:00 Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-22 20:08 ` Re: GENERATE AS David G. Johnston <david.g.johnston@gmail.com>
2023-06-22 20:13 ` Re: GENERATE AS Ilya Kosmodemiansky <ik@dataegret.com>
2023-06-22 20:45 ` Re: GENERATE AS Ron <ronljohnsonjr@gmail.com>
2023-06-23 02:47 ` Re: GENERATE AS J T <jorge.torralba@gmail.com>
0 siblings, 4 replies; 9+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-06-22 20:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi, I have this issue and now I'm just wasting time. Can you tell me what I'm doing wrong?
I'd like to subtract a column timestamp hour from current hour to give me hours elapased.
How do I do this easily?
I think I've tried every combination of types and casting.
Thanks in advance.
---------------------------
1. ALTER TABLE matt
add column matt_time timestamp with time zone default current_timestamp;
2. select matt_time FROM matt;
2023-06-22 14:31:16.548622-04 timestamp with time zone
3. Select (date_part('hour', current_timestamp)::INT - date_part('hour', matt_time)::INT) FROM matt
0 (same hour, so 0 is OK) INT
4. ALTER TABLE auto_auth.matt
ADD column matt_hour INT GENERATED ALWAYS AS (date_part('hour', current_timestamp)::INT - date_part('hour', matt_time)::INT) stored
ERROR: generation expression is not immutable
SQL state: 42P17
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: GENERATE AS
2023-06-22 20:00 GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-06-22 20:08 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2023-06-22 20:08 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Jun 22, 2023 at 1:00 PM Wetmore, Matthew (CTR) <
Matthew.Wetmore@express-scripts.com> wrote:
> Hi, I have this issue and now I’m just wasting time. Can you tell me what
> I’m doing wrong?
>
Like the error message says - you are trying to use a generated
expression when one of the inputs is volatile (or, is not immutable).
It makes no sense to store "hours elapsed" when the concept of current time
(an ever changing concept) is being used in the formula. Create a view
with you expression then the view can always provide an accurate value for
elapsed time as of when the query is run.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: GENERATE AS
2023-06-22 20:00 GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-06-22 20:13 ` Ilya Kosmodemiansky <ik@dataegret.com>
2023-06-22 20:25 ` Re: GENERATE AS David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 1 reply; 9+ messages in thread
From: Ilya Kosmodemiansky @ 2023-06-22 20:13 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Matthew,
> On 22. Jun 2023, at 22:00, Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:
> 4. ALTER TABLE auto_auth.matt
> ADD column matt_hour INT GENERATED ALWAYS AS (date_part('hour', current_timestamp)::INT - date_part('hour', matt_time)::INT) stored
>
> ERROR: generation expression is not immutable
> SQL state: 42P17
You can use only immutable functions to generate columns. Timestamp with time zone as an argument makes function not immutable because timezone could be set locally and results could differ depending on timezone
best regards,
Ilya
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: GENERATE AS
2023-06-22 20:00 GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-22 20:13 ` Re: GENERATE AS Ilya Kosmodemiansky <ik@dataegret.com>
@ 2023-06-22 20:25 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-22 21:15 ` Re: GENERATE AS Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2023-06-22 20:25 UTC (permalink / raw)
To: Ilya Kosmodemiansky <ik@dataegret.com>; +Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; pgsql-admin@lists.postgresql.org
On Thu, Jun 22, 2023 at 1:13 PM Ilya Kosmodemiansky <ik@dataegret.com>
wrote:
> Timestamp with time zone as an argument makes function not immutable
> because timezone could be set locally and results could differ depending
> on timezone
>
>
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.
You should not store data that changes simply due to the passage of time
(i.e., relies on external-to-the-record information).
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: GENERATE AS
2023-06-22 20:00 GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-22 20:13 ` Re: GENERATE AS Ilya Kosmodemiansky <ik@dataegret.com>
2023-06-22 20:25 ` Re: GENERATE AS David G. Johnston <david.g.johnston@gmail.com>
@ 2023-06-22 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-22 22:00 ` GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2023-06-22 21:15 UTC (permalink / raw)
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
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
* GENERATE AS
2023-06-22 20:00 GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-22 20:13 ` Re: GENERATE AS Ilya Kosmodemiansky <ik@dataegret.com>
2023-06-22 20:25 ` Re: GENERATE AS David G. Johnston <david.g.johnston@gmail.com>
2023-06-22 21:15 ` Re: GENERATE AS Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-06-22 22:00 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 0 replies; 9+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-06-22 22:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: Ilya Kosmodemiansky <ik@dataegret.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: GENERATE AS
2023-06-22 20:00 GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-06-22 20:45 ` Ron <ronljohnsonjr@gmail.com>
2023-06-22 20:54 ` Re: GENERATE AS Ron <ronljohnsonjr@gmail.com>
3 siblings, 1 reply; 9+ messages in thread
From: Ron @ 2023-06-22 20:45 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/22/23 15:00, Wetmore, Matthew (CTR) wrote:
>
> Hi, I have this issue and now I’m just wasting time. Can you tell me what
> I’m doing wrong?
>
> I’d like to subtract a column timestamp hour from current hour to give me
> hours elapased.
>
> How do I do this easily?
>
> I think I’ve tried every combination of types and casting.
>
> Thanks in advance.
>
> ---------------------------
>
> 1. ALTER TABLE matt
>
> add column matt_time timestamp with time zone default
> current_timestamp;
>
Besides what Ilya and David mentioned, CURRENT_TIMESTAMP is based on when
the transaction started. You might have wanted clock_timestamp().
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: GENERATE AS
2023-06-22 20:00 GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-22 20:45 ` Re: GENERATE AS Ron <ronljohnsonjr@gmail.com>
@ 2023-06-22 20:54 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Ron @ 2023-06-22 20:54 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/22/23 15:45, Ron wrote:
> On 6/22/23 15:00, Wetmore, Matthew (CTR) wrote:
>>
>> Hi, I have this issue and now I’m just wasting time. Can you tell me what
>> I’m doing wrong?
>>
>> I’d like to subtract a column timestamp hour from current hour to give me
>> hours elapased.
>>
>> How do I do this easily?
>>
>> I think I’ve tried every combination of types and casting.
>>
>> Thanks in advance.
>>
>> ---------------------------
>>
>> 1. ALTER TABLE matt
>>
>> add column matt_time timestamp with time zone default
>> current_timestamp;
>>
>
> Besides what Ilya and David mentioned, CURRENT_TIMESTAMP is based on when
> the transaction started. You might have wanted clock_timestamp().
Note the difference between bar_time and bar_time2 in rows 3 and 4.
foo=# alter table bar add column bar_time timestamp with time zone default
clock_timestamp();
ALTER TABLE
foo=# alter table bar add column bar_time2 timestamp with time zone default
current_timestamp;
ALTER TABLE
foo=# begin;
BEGIN
foo=*# insert into bar (f1, f2) values (3, 'jj');
INSERT 0 1
foo=*# insert into bar (f1, f2) values (4, 'kk');
INSERT 0 1
foo=*# commit;
COMMIT
foo=# select * from bar;
f1 | f2 | bar_time | bar_time2
----+----+-------------------------------+-------------------------------
1 | gg | 2023-06-22 15:48:43.509351-05 | 2023-06-22 15:50:27.983162-05
2 | hh | 2023-06-22 15:50:38.675741-05 | 2023-06-22 15:50:38.675487-05
3 | jj | 2023-06-22 15:50:57.886325-05 | 2023-06-22 15:50:49.599807-05
4 | kk | 2023-06-22 15:51:10.374681-05 | 2023-06-22 15:50:49.599807-05
(4 rows)
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: GENERATE AS
2023-06-22 20:00 GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-06-23 02:47 ` J T <jorge.torralba@gmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: J T @ 2023-06-23 02:47 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-06-23 02:47 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-22 20:00 GENERATE AS Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-22 20:08 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-22 20:13 ` Ilya Kosmodemiansky <ik@dataegret.com>
2023-06-22 20:25 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-22 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-22 22:00 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-22 20:45 ` Ron <ronljohnsonjr@gmail.com>
2023-06-22 20:54 ` Ron <ronljohnsonjr@gmail.com>
2023-06-23 02:47 ` J T <jorge.torralba@gmail.com>
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