DELETE and INSERT - is this simple query statement sufficient to preserve atomicity? I intend to update a table by going for a DELETE then INSERT on postgres in a single query. Obviously I want the DELETE to only take place if the INSERT is successful, i.e. preserve atomicity.

Is writing the query as below, sufficient for this?

DELETE FROM table_one WHERE some_id = 1;
INSERT INTO table_one(col1, col2, col3) VALUES (val1, val2, val3), (val4, val5, val6), ...


Thank you very much.

EDIT:

After reading around, I believe I am supposed to use transactions. Is this correct?

BEGIN;
DELETE FROM table_one WHERE some_id = 1;
INSERT INTO table_one(col1, col2, col3) VALUES (val1, val2, val3), (val4, val5, val6), ...
COMMIT;


So if DELETE fails, then the entire transaction fails?
Yes you can use transactions.

And yes if one of transaction statements fails all of it will rollback. Meaning : Nothing will happen or everything will be done.


  Your code :


BEGIN;
DELETE FROM table_one WHERE some_id = 1;
INSERT INTO table_one(col1, col2, col3) VALUES (val1, val2, val3), (val4, val5, val6), ...
COMMIT;


See following link for more information about transactions: Transactions in PostgreSql