Re: Column value derived from generated column in INSERT?

On 10/19/22 12:30, Mark Raynsford wrote:
> On 2022-10-19T11:58:07 -0700
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Wed, Oct 19, 2022 at 10:36 AM Mark Raynsford <co+org(dot)postgresql(at)io7m(dot)com>
>> 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. :)

create table t (
x integer not null generated always as identity,
y integer not null
);
insert into t (y) values (t.x * 2);

ERROR: invalid reference to FROM-clause entry for table "t"
LINE 1: insert into t (y) values (t.x * 2);
^
HINT: There is an entry for table "t", but it cannot be referenced from
this part of the query.

insert into t (y) values (x * 2);
ERROR: column "x" does not exist
LINE 1: insert into t (y) values (x * 2);
^
HINT: There is a column named "x" in table "t", but it cannot be
referenced from this part of the query.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com