Thread
-
Can't find the right generated column syntax
Bzzzz <lazyvirus@gmx.com> — 2019-11-26T02:30:41Z
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
David G. Johnston <david.g.johnston@gmail.com> — 2019-11-26T02:41:00Z
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
Bzzzz <lazyvirus@gmx.com> — 2019-11-26T02:59:36Z
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
David G. Johnston <david.g.johnston@gmail.com> — 2019-11-26T03:31:31Z
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
David G. Johnston <david.g.johnston@gmail.com> — 2019-11-26T03:35:50Z
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
Bzzzz <lazyvirus@gmx.com> — 2019-11-26T03:39:42Z
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
David G. Johnston <david.g.johnston@gmail.com> — 2019-11-26T03:58:29Z
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
Laurenz Albe <laurenz.albe@cybertec.at> — 2019-11-26T07:10:19Z
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