Thread: Can't find the right generated column syntax

Can't find the right generated column syntax

From

Bzzzz

Date:

26 November 2019, 05:30:41

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 ?)

Jean-Yves

Re: Can't find the right generated column syntax

From

"David G. Johnston"

Date:

26 November 2019, 05:41:00

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 and also show which version your are using. Provide the error your are getting as well.

Also:

https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRUCT

David J.

Re: Can't find the right generated column syntax

From

Bzzzz

Date:

26 November 2019, 05:59:36

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

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

Re: Can't find the right generated column syntax

From

"David G. Johnston"

Date:

26 November 2019, 06:31:31

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

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.

Re: Can't find the right generated column syntax

From

"David G. Johnston"

Date:

26 November 2019, 06:35:50

On Monday, November 25, 2019, Bzzzz <lazyvirus@gmx.com> wrote:

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

Correct, i think, you recognized that removing select is what the error was telling you.

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(') )...

Also probably correct if you recognize that [( doesn’t make sense as a range boundary specification.

David J.

Re: Can't find the right generated column syntax

From

Bzzzz

Date:

26 November 2019, 06:39:42

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

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

Re: Can't find the right generated column syntax

From

"David G. Johnston"

Date:

26 November 2019, 06:58:29

On Monday, November 25, 2019, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Monday, November 25, 2019, Bzzzz <lazyvirus@gmx.com> wrote:

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(') )...

Also probably correct if you recognize that [( doesn’t make sense as a range boundary specification.

Nevermind this one - getting the quoting correct with a literal input expression is why using the function is much easier. Even swapping ) for ( isn’t enough though its close - just need to get a and b to be treated as column references.

David J.

Re: Can't find the right generated column syntax

From

Laurenz Albe

Date:

26 November 2019, 10:10:19

On Tue, 2019-11-26 at 03:30 +0100, Bzzzz wrote:
> 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 ?)

That should be as simple as

  GENERATED ALWAYS AS (tstzrange(date_start, date_end)) STORED

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com