The exact guarantees you get from Postgres's default isolation level are pretty detailed. The manual* doesn't mention locking on constraints or unique indexes, but I can make guesses based on how I'd expect it to work. I don't see what it has to do with the language being declarative. It's not unique to Postgres, though, cause MySQL is similar.

* https://www.postgresql.org/docs/current/transaction-iso.html...