I am by no means a database expert so I might be asking something dumb.
So I am using PostgreSQL and I want to do the insert to one table, keep the ID (ID is incrementing automatically) and insert a tuple to another table (tuple has that ID in it).
Obviously I want to keep things atomic.
WITH inserted AS ( INSERT INTO "public"."user" (email, first_name, last_name, username) VALUES (?, ?, ?, ?) RETURNING id ) INSERT INTO "public"."security" (user_id, security) VALUES(( SELECT id FROM inserted ), ?);
Question is, can this even work? What will happen if the second insert fails? Will the first remain persisted?
The obvious answer is that I might put this into transaction and commit/rollback but will this be sufficient?