Column value derived from generated column in INSERT?
Hello! I'd like to be able to do something sort of analogous to this: create table t ( x integer not null generated always as identity, y integer not null ); insert into t (y) values (t.x * 2); In the real project, the "t.x * 2" expression is obviously something a lot more complex, but I think it illustrates the point: I'd like to be able to refer to the generated value of a column within the INSERT statement that's going to cause it to be generated. Is there a way to do this with a single statement right now? I can think of various ways to do it with multiple statements, but a single statement would be preferable. -- Mark Raynsford | https://www.io7m.com
Re: Column value derived from generated column in INSERT?
On 2022-10-19T11:58:07 -0700 "David G. Johnston" wrote: > On Wed, Oct 19, 2022 at 10:36 AM Mark Raynsford > wrote: > > > insert into t (y) values (t.x * 2); > > > > I can think of various ways to do it with multiple statements, but a > > single statement would be preferable. > > > > > No, by extension of the documented constraint: "The generation expression > can refer to other columns in the table, but not other generated columns." > Hello! Just want to confirm that I wasn't misunderstood. The documentation in CREATE TABLE has the sentence you quoted above, and unless I'm misunderstanding that's saying that the expression used to generate values in GENERATED (ALWAYS AS) columns can't refer to other GENERATED columns. That's fine, but that's not what I was asking. In the table above, `x` is generated without references to other columns, but for the non-GENERATED `y` value, I want to refer to the value that `x` will have when I calculate a value for the `y` column in the INSERT statement. If that's not doable, that's fine, I just want to be sure. :) -- Mark Raynsford | https://www.io7m.com
Re: Column value derived from generated column in INSERT?
On 2022-10-19T12:43:31 -0700 Adrian Klaver wrote: > > HINT: There is an entry for table "t", but it cannot be referenced from > this part of the query. > > HINT: There is a column named "x" in table "t", but it cannot be > referenced from this part of the query. Yes, I saw those, hence asking on the list if there was a way to do it. I'll handle it with multiple statements. -- Mark Raynsford | https://www.io7m.com
