Re: Can't find the right generated column syntax

On Mon, 25 Nov 2019 20:31:31 -0700
"David G. Johnston" <[email protected]> wrote:

> On Monday, November 25, 2019, Bzzzz <[email protected]> wrote:
>=20
> > 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]
>=20
> Probably should read the docs as to what an expression is and limit
> your random tests to just those...
>=20
> https://www.postgresql.org/docs/12/sql-expressions.html
>=20
>=20
> > 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...

You obviously did not read about ts(tz)ranges.

> That=E2=80=99s an impressive amount of random input that you seem to be
> claiming you don=E2=80=99t understand why you were in error...your issue =
is a
> lot bigger 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).
>=20
> I=E2=80=99m not running v12 but probably...as ( tstzrange(a,b,=E2=80=99[)=
=E2=80=99) )

That did the trick, thanks.

> using a function expression is the easiest.

Yeah, but I'm up for more than 48h now=E2=80=A6

JY