Allow null in unique column

Question

I've created the following table:

CREATE TABLE MMCompany (    CompanyUniqueID BIGSERIAL PRIMARY KEY NOT NULL,     Name VARCHAR (150) NOT NULL,    PhoneNumber VARCHAR(20) NOT NULL UNIQUE,     Email VARCHAR(75) UNIQUE,    CompanyLogo BYTEA  ); 

The email column is unique and it causes a "bug" in my scenario since there could only be one record with null. I'm trying to achieve records of companies without the same email but at the same time allow a companies to have no email.

How can I achieve that?

Answer 1

This is a misunderstanding.
The UNIQUE constraint does exactly what you want. Multiple NULL values can coexist 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 conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.

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.