MIME-Version: 1.0
From: Celia McInnis <celia.mcinnis@gmail.com>
Date: Mon, 22 Apr 2024 12:05:39 -0400
Message-ID: <CAGD6t7L3W=ftbvkrWRHwAAT0SAwdC0AWiw7oek7hRWMrBU9dUQ@mail.gmail.com>
Subject: adding a generated column to a table?
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000007ca7260616b19c6e"
Archived-At: <https://www.postgresql.org/message-id/CAGD6t7L3W%3DftbvkrWRHwAAT0SAwdC0AWiw7oek7hRWMrBU9dUQ%40mail.gmail.com>
Precedence: bulk

--0000000000007ca7260616b19c6e
Content-Type: text/plain; charset="UTF-8"

If I have a table containing a date field, say:
create temporary table tmp1 as select now()::date as evtdate;
SELECT 1

select DATE_PART('year', evtdate)::integer as year from tmp1;
 year
------
 2024
(1 row)

Is there some way of doing something like the following?:

alter table tmp1 add column year integer generated always as
DATE_PART('year', evtdate)::integer STORED;
ERROR:  syntax error at or near "DATE_PART"
LINE 1: ... tmp1 add column year integer generated always as DATE_PART(...

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

<div dir=3D"ltr"><div></div><div>If I have a table containing a date field,=
 say:</div><div>create temporary table tmp1 as select now()::date as evtdat=
e;<br>SELECT 1<br><br></div><div>select DATE_PART(&#39;year&#39;, evtdate):=
:integer as year from tmp1;<br>=C2=A0year <br>------<br>=C2=A02024<br>(1 ro=
w)</div><div><br></div><div>Is there some way of doing something like the f=
ollowing?:<br><div><br></div></div><div>alter table tmp1 add column year in=
teger generated always as DATE_PART(&#39;year&#39;, evtdate)::integer STORE=
D;<br>ERROR: =C2=A0syntax error at or near &quot;DATE_PART&quot;<br>LINE 1:=
 ... tmp1 add column year integer generated always as DATE_PART(...<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 <br></div=
></div>

--0000000000007ca7260616b19c6e--