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.g.johnston@gmail.com> wrote:
> 
>> On Wed, Oct 19, 2022 at 10:36 AM Mark Raynsford <co+org.postgresql@io7m.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.klaver@aklaver.com