There are several ways of doing this in Db2 for LUW.
Firstly, there is a function IDENTITY_VAL_LOCAL()
that returns one most recently assigned identity value; it can be used when inserting single records:
insert into parent_tab (some_column) values ('foobar');
insert into child_tab (parent_id, some_other_col)
values (identity_val_local(), 42);
assuming parent_tab
in the example has an identity column id
.
Another option is to use a data change table:
with parent_res (parent_id) as (
select id from final table (
insert into parent_tab (some_column) values ('foobar')
)
)
insert into child_tab (parent_id, some_other_col)
select parent_id, 42 from parent_res;
The data change table will contain as many rows as are affected by the corresponding DML statement.
In Postgres a similar result can be achieved by using the RETURNING
clause of the INSERT
statement:
with parent_res (parent_id) as (
insert into parent_tab (some_col) values ('foo') returning id
)
insert into child_tab (parent_id, some_other_col)
select parent_id, 42 from parent_res
Since the Postgres IDENTITY
(formerly known as SERIAL
) columns are backed by SQL sequences, you can also use the LASTVAL()
function to retrieve the most recently assigned identity (sequence) value in the session.