How do I handle an INSERT to 2 tables within a transaction?

Question

I have 3 tables in a Postgres DB:

• UserType -> ID, creation, delete, update, name
• User -> ID, creation, deleted, update, user_type_id
• Employee -> ID, creation, deleted, update, user_id (fk)

I'm trying to find a way to:

• Create a tx
• Execute the following code:

INSERT INTO users (user_type_id)
VALUES ((SELECT id FROM user_types where name = 'user'));

With the result of that User, proceed and create an Employee with a relation employee.user_id = last_inserted_user_id()

The issue here is that I can't have the last inserted ID because there's no way to insert something that's not committed.

What should I do? Create the user, commit the tx and if something fails during the employee creation delete that user?

Answer 1

Assuming your ID columns generate default values, like a serial or IDENTITY column.

Probably simplest to use a CTE with RETURNING:

WITH ins_usr AS (
   INSERT INTO users (user_type_id)
   SELECT id FROM user_types WHERE name = 'user'  -- untangled
   RETURNING id
   )
INSERT INTO employees (user_id)  -- more columns?
SELECT i.id                      -- more values?
FROM   ins_usr i;

Since this is a single statement it's atomic - with or without its own transaction wrapper.

Answer 2

The issue here is that I can't have the last inserted ID because there's no way to insert something that's not committed.

if it's happening in the same transaction that's inserting the user there will be no problem.

Answer 3

The issue here is that I can't have the last inserted ID because there's no way to insert something that's not committed.

Like Jasen mentioned in his answer, if the INSERTs both take place within the same transaction, then they're in the same scope and you can access the newly inserted row.

But I wouldn't know the last inserted user id before commmiting

Yes, you can obtain that using the RETURNING clause, like so:

INSERT INTO users (user_type_id)
VALUES ((SELECT id FROM user_types where name = 'user'))
RETURNING id;

For more ways to get the last inserted values, please see this StackOverflow answer on a similar question. But not, the RETURNING clause is the most recommended way for its automocity.