if data is inserted concurrently by two or many people [can it] have any problems[?]
Yes, it will have problems. That statement is not safe in the presence of concurrent execution, in that it can raise a unique violation error (if a suitable unique constraint is present) or insert duplicates (if no unique constraint is present).
This fragment:
NOT IN (SELECT name FROM t)
can run concurrently in multiple sessions. Neither session has yet INSERT
ed a tuple. Even if it had, it would be uncommitted, so the SELECT
wouldn't see it in its snapshot. Both instances would return false
and thus execute the INSERT
.
(also, NOT IN
can produce unexpected results if the column is nullable, and can be slow. Use NOT EXISTS
or a left-anti-join).
Doing everything in one statement - wCTE or otherwise - doesn't make concurrency issues go away, though it can make it a bit harder to trigger them. Even two simple UPDATE
s can have interactions related to their concurrent execution (in the form of deadlocks). It's a common mistake to think that the "atomacity" of ACID means that somehow statements execute in a logical instant. That's just not the case. The property of atomicity refers to a transaction either committing with all changes taking effect, or rolling back with no changes taking effect.
See: How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?