Re: Can't find the right generated column syntax - Mailing list pgsql-novice
From | Bzzzz |
---|---|
Subject | Re: Can't find the right generated column syntax |
Date | |
Msg-id | 20191126035936.2e27371d@msi.defcon1.lan Whole thread Raw |
In response to | Re: Can't find the right generated column syntax ("David G. Johnston" <david.g.johnston@gmail.com>) |
Responses |
Re: Can't find the right generated column syntax
Re: Can't find the right generated column syntax |
List | 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 by date: