MIME-Version: 1.0
References: 
 <CAMdfv4XEHpa8XSYf1C5EG5CE_VbcpptxiijUNOzj3HEvwLOaxQ@mail.gmail.com>
 <2aa85cf2-dfb1-474e-b190-0a96d7afeb2b@jakobs.com>
In-Reply-To: <2aa85cf2-dfb1-474e-b190-0a96d7afeb2b@jakobs.com>
From: Mauricio Fernandez <mmauricio.fernandez@gmail.com>
Date: Wed, 3 Sep 2025 13:51:19 -0400
Message-ID: 
 <CAMdfv4Vjku_9u9E3NK-g_63owpyqnLEnNuCku8t2ksO==DnUPg@mail.gmail.com>
Subject: Re: Adding timestamp column
To: Holger Jakobs <holger@jakobs.com>,
 "David G. Johnston" <david.g.johnston@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000001fe376063de941c1"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMdfv4Vjku_9u9E3NK-g_63owpyqnLEnNuCku8t2ksO%3D%3DDnUPg%40mail.gmail.com>
Precedence: bulk

--0000000000001fe376063de941c1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thank you Holger and David, the error was the missing clausule STORED

The timecreated data type is Int8

King regards

Mauricio Fern=C3=A1ndez

El mi=C3=A9, 3 sept 2025 a las 13:46, Holger Jakobs (<holger@jakobs.com>)
escribi=C3=B3:

> Am 03.09.25 um 17:50 schrieb Mauricio Fernandez:
> > Hi admins, greetings.
> >
> > I'm trying to add a timestamp column in an existent table.
> >
> > This query works fine:
> >
> > select timecreated, timezone('America/Santiago'::text,
> > to_timestamp(timecreated::double precision)) datecreated
> > from MDL_LOGSTORE_STANDARD_LOG;
> >
> > But, when I want to alter the table I get  a syntax error
> >
> > alter table MDL_LOGSTORE_STANDARD_LOG
> >   add column datecreated timestamp
> >   generated always as ( timezone ('America/Santiago'::text,
> > to_timestamp(timecreated::double precision)));
> >
> > SQL Error [42601]: ERROR: syntax error at end of input
> >   Position: 185
> >
> > I would appreciate some tips
> >
> > Thanks in advanced
> >
> > kind regards
> >
> > Mauricio Fern=C3=A1ndez
>
> Instead of
>
> alter table MDL_LOGSTORE_STANDARD_LOG
>    add column datecreated timestamp
>    generated always as ( timezone ('America/Santiago'::text,
> to_timestamp(timecreated::double precision)));
>
> I would suggest
>
> ALTER TABLE MDL_LOGSTORE_STANDARD_LOG
> ADD COLUMN datecreated TIMESTAMP
>    GENERATED ALWAYS AS (timecreated AT TIME ZONE 'America/Santiago')
> STORED;
>
> You didn't tell us the data type of the column timecreated. It should be
> 'TIMESTAMPTZ DEFAULT current_timestamp' for it to work properly.
>
> As of Version 18 you can leave out STORED, creating a virtual generated
> column.
>
> Kind Regards,
>
> Holger
>
> --
>
> Holger Jakobs, Bergisch Gladbach
>
>
>
>

--0000000000001fe376063de941c1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><br><div>Thank you Holger and David, the error was the mis=
sing clausule STORED</div><div><br></div><div>The timecreated data type is =
Int8</div><div><br></div><div>King=C2=A0regards</div><div><br></div><div>Ma=
uricio Fern=C3=A1ndez</div></div><br><div class=3D"gmail_quote"><div dir=3D=
"ltr" class=3D"gmail_attr">El mi=C3=A9, 3 sept 2025 a las 13:46, Holger Jak=
obs (&lt;<a href=3D"mailto:holger@jakobs.com" target=3D"_blank">holger@jako=
bs.com</a>&gt;) escribi=C3=B3:<br></div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex">Am 03.09.25 um 17:50 schrieb Mauricio Fernandez:<br>
&gt; Hi admins, greetings.<br>
&gt;<br>
&gt; I&#39;m trying to add a timestamp column in an existent table.<br>
&gt;<br>
&gt; This query works fine:<br>
&gt;<br>
&gt; select timecreated, timezone(&#39;America/Santiago&#39;::text, <br>
&gt; to_timestamp(timecreated::double precision)) datecreated<br>
&gt; from MDL_LOGSTORE_STANDARD_LOG;<br>
&gt;<br>
&gt; But, when I want to alter the table I get=C2=A0 a syntax=C2=A0error<br=
>
&gt;<br>
&gt; alter table MDL_LOGSTORE_STANDARD_LOG<br>
&gt; =C2=A0 add column datecreated timestamp<br>
&gt; =C2=A0 generated always as ( timezone (&#39;America/Santiago&#39;::tex=
t, <br>
&gt; to_timestamp(timecreated::double precision)));<br>
&gt;<br>
&gt; SQL Error [42601]: ERROR: syntax error at end of input<br>
&gt; =C2=A0 Position: 185<br>
&gt;<br>
&gt; I would appreciate some tips<br>
&gt;<br>
&gt; Thanks in advanced<br>
&gt;<br>
&gt; kind regards<br>
&gt;<br>
&gt; Mauricio Fern=C3=A1ndez<br>
<br>
Instead of<br>
<br>
alter table MDL_LOGSTORE_STANDARD_LOG<br>
=C2=A0=C2=A0 add column datecreated timestamp<br>
=C2=A0=C2=A0 generated always as ( timezone (&#39;America/Santiago&#39;::te=
xt, <br>
to_timestamp(timecreated::double precision)));<br>
<br>
I would suggest<br>
<br>
ALTER TABLE MDL_LOGSTORE_STANDARD_LOG<br>
ADD COLUMN datecreated TIMESTAMP<br>
=C2=A0=C2=A0 GENERATED ALWAYS AS (timecreated AT TIME ZONE &#39;America/San=
tiago&#39;) STORED;<br>
<br>
You didn&#39;t tell us the data type of the column timecreated. It should b=
e <br>
&#39;TIMESTAMPTZ DEFAULT current_timestamp&#39; for it to work properly.<br=
>
<br>
As of Version 18 you can leave out STORED, creating a virtual generated <br=
>
column.<br>
<br>
Kind Regards,<br>
<br>
Holger<br>
<br>
-- <br>
<br>
Holger Jakobs, Bergisch Gladbach<br>
<br>
<br>
<br>
</blockquote></div>

--0000000000001fe376063de941c1--