This is a misunderstanding.
The UNIQUE constraint does exactly what you want. NULL values can coexist in multiple rows in a column defined UNIQUE.
The manual:
In general, a unique constraint is violated when there is more than
one row in the table where the values of all of the columns included
in the constraint are equal. However, two null values are not
considered equal in this comparison. That means even in the presence
of a unique constraint it is possible to store duplicate rows that
contain a null value in at least one of the constrained columns. This
behavior can be changed by adding the clauseNULLS NOT DISTINCT[…]
Bold emphasis mine.
Be aware that character types allow an empty string (''), which is not a NULL value and would trigger a unique violation just like any other non-null value when entered in more than one row.
Postgres 15 allows the clause NULLS NOT DISTINCT to alter this behavior (the opposite of what you requested). I updated the above quote. See:
- Create unique constraint with null columns