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