Re: Can't find the right generated column syntax
Bzzzz <[email protected]> Tue, 26 Nov 2019 03:59:36 +0100
| Newsgroups | gmane.comp.db.postgresql.novice |
|---|---|
| Organization | Anyone, anywhere BUT in banana demokratik republik of france and UERSS. |
| Message-ID | <[email protected]> |
On Mon, 25 Nov 2019 19:41:00 -0700 "David G. Johnston" <[email protected]> wrote: > On Monday, November 25, 2019, Bzzzz <[email protected]> wrote: >=20 > > 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=E2=80=A6 "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. >=20 > Also: >=20 > https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTR= UCT >=20 > David J. JY