How to make empty string as null value to allow duplicate empty strings in optional field?

Question

I have an email field that's optional. The regex works fine accepting the empty string (^$) but now the issue is that the empty string is considered a unique entry. It will only allow one user to register without entering an email address. I know I have to set it to null, but not sure how.

Something like this:

Duplicate entry '' for key 'users_email_unique' empty field

Error: Duplicate entry '' for key 'email'

Answer 1

You could use partial index:

CREATE UNIQUE INDEX idx_unq_tab_email ON tab(email) WHERE TRIM(email) <> '';

DBFiddle Demo

That way you still have UNIQUE constraint plus original value.

Answer 2

Create a trigger that converts blanks to nulls on insert or update:

create trigger null_email
before insert or update on users
for each row
new.email = nullif(old.email, '')

Or convert blanks to nulls on insert:

insert into users (..., email, ...)
values (..., nullif(?, ''), ...)

The trigger is the better way, because it handles data from any source, whereas method 2 would require the insert/update SQL of every application to conform to the "no blanks" rule.