1

I am trying to use transactions when inserting into a PostgresQL DB, however due to dependencies, I ran into some issues: During the transaction I am inserting into multiple tables. The first table into which data is inserted is a table that saves company profile information (name, etc.). One of the other tables has a foreign key column that references to the key of the company which I am inserting in the same step. The company key is a BigInt auto-increment key, which leads to the following problem.

If I use a transaction to insert into all the tables at once, I cannot query for the company key (the insert-command has not come into effect yet), which is needed to insert into the other tables. On the other hand using multiple transactions might lead to an inconsistent state if something fails...

Is there a way to query for what the next assigned BigInt key in the company table would be, so that I can put everything into one transaction? How would you suggest resolving this issue?

Any help is highly appreciated! Thank you in advance!

Sam

Sam
  • 111
  • 1

1 Answers1

2

You don't need to query the table, to get the generated id, you can get that using the lastval() function:

insert into company_profile (....)
values (...);
insert into related_table(company_id, ...)
values (lastval(), ...);