isn't "insert into where not exists" atomic?

Hello,

I just received an error message:

   PGError: ERROR:  duplicate key value violates unique constraint "chu_user_id_chat_room_id"
DETAIL:  Key (user_id, chat_room_id)=(8, 2) already exists.
CONTEXT:  SQL statement "insert into chat_room_users (user_id, chat_room_id, active_at) (select NEW.user_id,
NEW.chat_room_id,now() where not exists (select 1 from chat_room_users where user_id = NEW.user_id and chat_room_id =
NEW.chat_room_id))"
PL/pgSQL function "trf_chat_room_users_insert" line 3 at SQL statement
: INSERT INTO "chat_room_users" ("user_id", "chat_room_id", "active_at") VALUES (8, 2, NULL)


The important line is:
insert into chat_room_users (user_id, chat_room_id, active_at) (select NEW.user_id, NEW.chat_room_id, now() where not
exists(select 1 from chat_room_users where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id)) 


I always thought this is atomic and can not fail. Was I wrong?

If it isn't then I have to rewrite my triggers. Do I have to use "lock
table" instead of the above to avoid errors in parallel inserts?

The trigger looks like:

create or replace function trf_chat_room_users_insert() returns trigger
as $$
begin
         if NEW.active_at is null then
                 insert into chat_room_users (user_id, chat_room_id,
active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists
(select 1 from chat_room_users where user_id = NEW.user_id and
chat_room_id = NEW.chat_room_id));
                 if not found then
                         update chat_room_users set active_at = now()
where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id;
                 end if;
                 return null;
         end if;
         return NEW;
end;
$$ language plpgsql;

And it meant to be "insert or update".

         Mage