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'

Valenza

Are you saying that setting the field to null would work? If so, why aren't you doing that then? – Merilynmeringue

Answer 1

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.

Shortcoming

I'd agree with you on this, papering over the problem with a partial index seems like a gift that keeps on giving (grief and confusion). – Dena

Answer 2

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.

Verniavernice

The downside of this is that all email is null queries would have to become email is null or trim(email) = ''. I think you'd be better off in the long run if you cleaned up the data before putting it in the table. – Dena