Here's an example:
create database users;
create table users (id int unique);
Then running these together:
insert into users values(1);
insert into users values(1);
I expected the first insert to succeed, but the second to fail. However what I am seeing is that they are run atomically, and no row is inserted. Here are the logs:
2021-11-08 23:04:37.825 UTC [181] LOG: statement: insert into users values(1);
insert into users values(1);
2021-11-08 23:04:37.825 UTC [181] ERROR: duplicate key value violates unique constraint "users_id_key"
2021-11-08 23:04:37.825 UTC [181] DETAIL: Key (id)=(1) already exists.
2021-11-08 23:04:37.825 UTC [181] STATEMENT: insert into users values(1);
insert into users values(1);
What's confusing is that there is no BEGIN; or COMMIT;. Are statements with multiple commands run atomically?
-- EDIT --
I am using Postico client to run these statements.