Thread: Column value derived from generated column in INSERT?

Column value derived from generated column in INSERT?

From
Mark Raynsford
Date:
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?

From
"David G. Johnston"
Date:
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."

David J.

Re: Column value derived from generated column in INSERT?

From
Mark Raynsford
Date:
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. :)

-- 
Mark Raynsford | https://www.io7m.com




Re: Column value derived from generated column in INSERT?

From
Adrian Klaver
Date:
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




Re: Column value derived from generated column in INSERT?

From
Mark Raynsford
Date:
On 2022-10-19T12:43:31 -0700
Adrian Klaver <adrian.klaver@aklaver.com> 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




Re: Column value derived from generated column in INSERT?

From
Adrian Klaver
Date:
On 10/19/22 12:48, Mark Raynsford wrote:
> On 2022-10-19T12:43:31 -0700
> Adrian Klaver <adrian.klaver@aklaver.com> 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.

Using a trigger.

> 
> I'll handle it with multiple statements.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Column value derived from generated column in INSERT?

From
Adrian Klaver
Date:
On 10/19/22 12:58 PM, Adrian Klaver wrote:
> On 10/19/22 12:48, Mark Raynsford wrote:
>> On 2022-10-19T12:43:31 -0700
>> Adrian Klaver <adrian.klaver@aklaver.com> 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.
> 
> Using a trigger.

To expand:

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

select * from t;

x | y
---+---
  1 | 1
(1 row)



CREATE FUNCTION identity_test( )
  RETURNS trigger
  LANGUAGE plpgsql
AS $function$
BEGIN
     NEW.y = NEW.x * 2;
     RETURN NEW;
END;

$function$
;

create trigger identity_trg before insert on t for each row execute 
function identity_test();

insert into t(y) values (0);

select * from t;

  x | y
---+---
  1 | 1
  2 | 4
(2 rows)

> 
>>
>> I'll handle it with multiple statements.
>>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Column value derived from generated column in INSERT?

From
Erik Wienhold
Date:
> On 19/10/2022 23:51 CEST Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 10/19/22 12:58 PM, Adrian Klaver wrote:
> > On 10/19/22 12:48, Mark Raynsford wrote:
> >> On 2022-10-19T12:43:31 -0700
> >> Adrian Klaver <adrian.klaver@aklaver.com> 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.
> >
> > Using a trigger.
>
> To expand:
>
> create table t (
>      x integer not null generated always as identity,
>      y integer not null
>    );
> insert into t(y) values (1);
>
> select * from t;
>
> x | y
> ---+---
>   1 | 1
> (1 row)
>
>
>
> CREATE FUNCTION identity_test( )
>   RETURNS trigger
>   LANGUAGE plpgsql
> AS $function$
> BEGIN
>      NEW.y = NEW.x * 2;
>      RETURN NEW;
> END;
>
> $function$
> ;
>
> create trigger identity_trg before insert on t for each row execute
> function identity_test();
>
> insert into t(y) values (0);
>
> select * from t;
>
>   x | y
> ---+---
>   1 | 1
>   2 | 4
> (2 rows)

Make t.y a generated column and avoid the trigger:

    create table t (
      x int not null generated always as identity,
      y int not null generated always as (x * 2) stored;
    );

    insert into t (x) values (default), (default);

    select * from t;

     x | y
    ---+---
     1 | 2
     2 | 4
    (2 rows)

But I think Mark wants to specify the expression in the INSERT and not define
it as part of the database schema, if I understand it correctly.

--
Erik



Re: Column value derived from generated column in INSERT?

From
"David G. Johnston"
Date:
On Wed, Oct 19, 2022 at 12:34 PM Mark Raynsford <co+org.postgresql@io7m.com> 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."
>

 
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.

Yes, I got that, but if another generated column cannot reference the resultant computation of a generated column anything you write, which is by definition computed before the generated column, is not going to be able to see the result of the generated column either.  There was at least some hope for one generated depending upon another so the the expressions could maybe be evaluated in sequence.

Thus, while the docs do not explicitly state the non-functionality it can be inferred by what other non-functionality is stated.

David J.