0

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?

1
  • when you say transaction what do you mean? it is everything between BEGIN TRANSACTION and COMMIT or is it something else?
    – Jasen
    Commented Nov 29, 2022 at 21:14

3 Answers 3

2

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.

1
  • I will try this! Thanks!
    – Thomas
    Commented Nov 29, 2022 at 12:05
0

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.

2
  • But I wouldn't know the last inserted user id before commmiting
    – Thomas
    Commented Nov 29, 2022 at 12:04
  • the usual methods lastval('name of the sequence'). insert...returning, select... all work just fine inside transactions.
    – Jasen
    Commented Nov 29, 2022 at 21:12
0

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.

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.