Retrieve autoincremented primary key Id for each inserted row and use it in second table

[Anonymous]

Hi ,

Currently I have input source oracle data which I try to save it to table_A which is PostgreSQL database and this table has auto-incremented primary key.

We have below 2 questions:

1] Once we insert this row in Table_A how i can get auto-incremented value and save it Table_B.

2] Is there any feature/components available using which we can compare source data to destination data and decide if it is new data to be inserted or existing data which we need to update...

Thanks,

[TRF]

Hi,

1) I'm afraid you have to query the table to get the id for the new rows

2) Check for "Insert or update" (or "Update or insert") option of tPostgresqlOutput component

[Anonymous]

Thanks for suggestion but according to what you suggestes for #1 my concern is that i will be able to get those IDs after saving all the records but I would like to get that for each record and I see in talend using tPostgresqlOutput  it insert records in bulk like 10k at a time un til then nothing is saved so how we can save one row at a time and get ID in return.

[TRF]

You can only retrieve the fields which are in the schema but the autoincrement cannot be part of the flow (using a standard SQL query, you cannot mention serial column in Insert/Update statement).

You may reduce the "Commit every" parameter to 1 then query for the id row by row but it this will dramadically reduce job throughput (not a good idea).

[Anonymous]

So If I want to take latest ID after each row insert using below query  but how i can map this to the column do we need to use tmap  if yes how?

select max(id_store) from store;

[TRF]

If the id is declared as a serial (it is, isn't it?), you have a sequence associated to the field.

In your case it should be called "store_id_store_seq" (tablename + column name + seq).

It will be better to ask for the sequence last used value with the following query:

select currval('store_id_store_seq')

This kind of design could be a solution (sorry, I can't try by myself):

tPostgresqlRow will be called for each row (change the lookup model for this flow in the tMap):

It hope this works but once again, not sure as I can't try.

Let us know.

[Anonymous]

Hi,

My primary key column is bigserial and its has sequence which you mentioned..but when I do the suggested approach the store_id is always empty in my second table which is referenced table(refer below screenshot) when i want to save it but in the first table ID is saved properly. Please find below screenshots and query use for sequence.

Query: select nextval('rpcs.rp_store_id_store_seq')

screenshots:

Output table:

[Anonymous]

Is anybody have any solution or some other idea to implement this?

[Anonymous · Accepted solution]

Hi All,

Instead of using tpostgresqlrow I used regular tpostgresqlinput with below query and it worked. If somebody would like to use it in future i am just adding screenshot of job for their reference..

select last_value from schemaname.sequencename (here replace your schemaname and sequence name with your)

[TRF]

Hi,
You're right tpostgresqlinput is the way to retrieve result from the query.
Don't forget to mark your case as solved.
Kudos could be also appreciated for time left and given directions.