1

What is the best way to retrieve an auto-generated keys after an INSERT statement ?

After searching online for DB2 I found this https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_t0057053.html

Is there a way to do it without using java ?

Preferably I would work using python so ibm_db.

Can such a thing be done on a postgres database ?

0

2 Answers 2

4

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.

0

Returning is available in popular db . Like

INSERT INTO .. RETURNING *

Put what you want to return from db.

Store the result like

ResultSet rs = stmt.ecexuteQuery();
rs.next();
long pk = rs.getLong(1);

here i return rs.getLong(1);, Return what you want. You can find more information here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.