Re: Can't find the right generated column syntax

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

Yeah, I do that only to see the effect on others…
"rubber keyboard" are real POS compared to those before, especially
when you type very fast :(

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( SELECT tstzrange(a, b, '[)' ) ) stored); ERROR:
syntax error at or near "SELECT" LINE 1: ... b timestamptz, c tstzrange
generated always as ( SELECT tst...

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( (SELECT tstzrange(a, b, '[)')) ) stored); ERROR:
cannot use subquery in column generation expression LINE 1: ... b
timestamptz, c tstzrange generated always as ( (SELECT ts...

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( ( '[a,b('::tstzrange ) ) stored); ERROR:
malformed range literal: "[a,b(" LINE 1: ... timestamptz, c tstzrange
generated always as ( ( '[a,b('::t...

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( "[a,b("::tstzrange ) stored); ERROR: column
"[a,b(" does not exist LINE 1: ... b timestamptz, c tstzrange generated
always as ( "[a,b("::t...

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( ('[a,b('::tstzrange) ) stored); ERROR: malformed
range literal: "[a,b(" LINE 1: ...b timestamptz, c tstzrange generated
always as ( ('[a,b('::t...

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ([a,b(::tstzrange) stored); testjy(# ^C

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

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( ('[''a','b''('::tstzrange) ) stored); ERROR:
malformed range literal: "b'(" LINE 1: ...tamptz, c tstzrange generated
always as ( ('[''a','b''('::ts...

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( ('[''a','b''(') ) stored); ERROR: column "c" is
of type tstzrange but default expression is of type record HINT: You
will need to rewrite or cast the expression.

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( ('['"a","b"'(') ) stored); ERROR: syntax error at
or near ""a"" LINE 1: ...imestamptz, c tstzrange generated always as
( ('['"a","b"'('...

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( (["a","b"() ) stored); testjy(# ^C

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( ('["a","b"(') ) stored); ERROR: malformed range
literal: "["a","b"(" LINE 1: ...b timestamptz, c tstzrange generated
always as ( ('["a","b"(...

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( ('[a,b(') ) stored); ERROR: malformed range
literal: "[a,b(" LINE 1: ...b timestamptz, c tstzrange generated always
as ( ('[a,b(') )...

CREATE TABLE tst_timerange(a timestamptz, b timestamptz, c tstzrange
generated always as ( ('a,b') ) stored); ERROR: malformed range
literal: "a,b" LINE 1: ...b timestamptz, c tstzrange generated always as
( ('a,b') ) s...

> and also show which version
> your are using.

v.12 Debian package from the Pg repo.

> Provide the error your are getting as well.
>
> Also:
>
> https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRUCT
>
> David J.

JY