> On 19/10/2022 23:51 CEST Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx> 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