Re: Can't find the right generated column syntax

On Mon, 25 Nov 2019 20:31:31 -0700
"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Monday, November 25, 2019, Bzzzz <lazyvirus(at)gmx(dot)com> wrote:
>
> > On Mon, 25 Nov 2019 19:41:00 -0700
> > "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >
> > > On Monday, November 25, 2019, Bzzzz <lazyvirus(at)gmx(dot)com> 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’t 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...

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

> That’s an impressive amount of random input that you seem to be
> claiming you don’t 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).
>
> I’m not running v12 but probably...as ( tstzrange(a,b,’[)’) )

That did the trick, thanks.

> using a function expression is the easiest.

Yeah, but I'm up for more than 48h now…

JY