Re: Can't find the right generated column syntax
"David G. Johnston" <[email protected]> Mon, 25 Nov 2019 20:31:31 -0700
| Newsgroups | gmane.comp.db.postgresql.novice |
|---|---|
| Message-ID | <CAKFQuwaWK1tVFtbT16K2AnjqG20KA28ROuhmC29uiumQLch4sA@mail.gmail.com> |
--00000000000032c11b0598378429 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Monday, November 25, 2019, Bzzzz <[email protected]> wrote: > On Mon, 25 Nov 2019 19:41:00 -0700 > "David G. Johnston" <[email protected]> wrote: > > > On Monday, November 25, 2019, Bzzzz <[email protected]> wrote: > > > > > Hi list, > > > > > > I've got this table: > > > date_start timestamptz, > > > date_end temstamptz > > > > > > and I'd like to auto-generate a 3rd column with a tstzrange: > > > date_start timestamptz, > > > date_end temstamptz, > > > duration tstzrange GENERATED ALWAYS AS ( ? ) STORED > > > > > > but I can't find the right syntax to do so :/ (is it even possible ?) > > > > > > > > You should provide actual SQL without typos > > [... lots of stuff many of which aren=E2=80=99t expressions] Probably should read the docs as to what an expression is and limit your random tests to just those... https://www.postgresql.org/docs/12/sql-expressions.html > CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange > generated always as ( ('['a,b'('::tstzrange) ) stored); ERROR: syntax > error at or near "a" LINE 1: ...imestamptz, c tstzrange generated always > as ( ('['a,b'('::ts... > > Use the closing symbol of the pair for the upper bound... That=E2=80=99s an impressive amount of random input that you seem to be cla= iming you don=E2=80=99t understand why you were in error...your issue is a lot bi= gger than just not knowing how to read the syntax for the generated column feature ; for instance using learning technique whereby you demonstrate a correctly executed expression using a simple SELECT command first, before attempting to incorporate said working expression in the larger create table command (without SELECT since its presence turns an expression into a statement). I=E2=80=99m not running v12 but probably...as ( tstzrange(a,b,=E2=80=99[)= =E2=80=99) ) stored ... using a function expression is the easiest. David J. --00000000000032c11b0598378429 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Monday, November 25, 2019, Bzzzz <<a href=3D"mailto:[email protected]= ">[email protected]</a>> wrote:<br><blockquote class=3D"gmail_quote" sty= le=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On Mon= , 25 Nov 2019 19:41:00 -0700<br> "David G. Johnston" <<a href=3D"mailto:david.g.johnston@gmail.= com">[email protected]</a>> wrote:<br> <br> > On Monday, November 25, 2019, Bzzzz <<a href=3D"mailto:lazyvirus@gm= x.com">[email protected]</a>> wrote:<br> > <br> > > Hi list,<br> > ><br> > > I've got this table:<br> > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0date_start=C2=A0 =C2=A0 =C2=A0 t= imestamptz,<br> > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0date_end=C2=A0 =C2=A0 =C2=A0 =C2= =A0 temstamptz<br> > ><br> > > and I'd like to auto-generate a 3rd column with a tstzrange:<= br> > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0date_start=C2=A0 =C2=A0 =C2=A0 t= imestamptz,<br> > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0date_end=C2=A0 =C2=A0 =C2=A0 =C2= =A0 temstamptz,<br> > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0duration=C2=A0 =C2=A0 =C2=A0 =C2= =A0 tstzrange GENERATED ALWAYS AS ( ? ) STORED<br> > ><br> > > but I can't find the right syntax to do so :/ (is it even pos= sible ?)<br> > ><br> > ><br> > You should provide actual SQL without typos<br> <br></blockquote><div><br></div><div>[... lots of stuff many of which aren= =E2=80=99t expressions]</div><div><br></div><div>Probably should read the d= ocs as to what an expression is and limit your random tests to just those..= .</div><div><br></div><div><a href=3D"https://www.postgresql.org/docs/12/sq= l-expressions.html">https://www.postgresql.org/docs/12/sql-expressions.html= </a><br></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"m= argin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange<br> generated always as ( ('['a,b'('::tstzrange) ) stored); ERR= OR:=C2=A0 syntax<br> error at or near "a" LINE 1: ...imestamptz, c tstzrange generated= always<br> as ( ('['a,b'('::ts...<br> <br></blockquote><div><br></div><div>Use the closing symbol of the pair for= the upper bound...</div><div>=C2=A0</div><div>That=E2=80=99s an impressive= amount of random input that you seem to be claiming you don=E2=80=99t unde= rstand why you were in error...your issue is a lot bigger than just not kno= wing how to read the syntax for the generated column feature ; for instance= using learning technique whereby you demonstrate a correctly executed expr= ession using a simple SELECT command first, before attempting to incorporat= e said working expression in the larger create table command (without SELEC= T since its presence turns an expression into a statement).</div><div><br><= /div><div>I=E2=80=99m not running v12 but probably...as ( tstzrange(a,b,=E2= =80=99[)=E2=80=99) ) stored ...</div><div><br></div><div>using a function e= xpression is the easiest.</div><div><br></div><div>David J.</div> --00000000000032c11b0598378429--