Re: Can't find the right generated column syntax

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...

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,’[)’) ) stored ...

using a function expression is the easiest.

David J.