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?