Обсуждение: GENERATE AS

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

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;}

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;

On 6/22/23 15:00, Wetmore, Matthew (CTR) wrote:

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;}

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().

"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

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