It is kinda weird, if you don't know how it works. For instance:

Say you have a unique constraint on two columns (column_a, column_b). column_a is not nullable, column_b IS nullable. Obviously these values are unique:

(1, 2) (1, 3) (2, 2)

But these values ARE ALSO UNIQUE:

(1, NULL) (1, NULL)

It is obvious in hindsight (NULL isn't equal to NULL), but can be quite a surprise.

...

Oh wait, postgres 15 deals with just this situation. Huh.