I am using the following query to insert data to PostgreSQL tables user and password.
BEGIN;
INSERT INTO users (user_id, user_username, user_name, user_surname) VALUES (?, ?, ?, ?);
INSERT INTO passwords (user_id, user_password) VALUES (?, ?);
COMMIT;
I would like to cancel the transaction in case of user_id or user_username conflict. I know ON CONFLICT (user_id, user_username) DO NOTHING
may help in this case but I am afraid it won’t actually cancel the insertion to the password table.
How can I make it so in a case of conflict all of the transaction will be cancelled?
2
Answers
One way could to use
ABORT
instead ofDO NOTHING
. That would rollback the transaction. Please see https://stackoverflow.com/a/17259954/924036.ON CONFLICT DO NOTHING means that when conflict occurs while inserting into a table, the row will not be inserted. ON CONFLICT DO UPDATE means that when conflict occurs while inserting a row into the table, the existing row that conflicts with the new row will be updated with the newer values.
you can refer to ON CONFLICT docs