Re: Can't find the right generated column syntax
От | Bzzzz |
---|---|
Тема | Re: Can't find the right generated column syntax |
Дата | |
Msg-id | 20191126035936.2e27371d@msi.defcon1.lan обсуждение исходный текст |
Ответ на | Re: Can't find the right generated column syntax ("David G. Johnston" <david.g.johnston@gmail.com>) |
Ответы |
Re: Can't find the right generated column syntax
Re: Can't find the right generated column syntax |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления:
Предыдущее
От: "David G. Johnston"Дата:
Сообщение: Re: Can't find the right generated column syntax