Consider normalizing empty values to NULL when inserting into the database

[artur-intech]

In Rails when saving a model from a form and values are not provided by the user, an empty string is recorded to the database instead of a NULL (mixing blanks and NULLs can become confusing).

• Unique constraint check fails if there is more than one row with empty column

[virtualfeline]

Do we actually have any case where missing value is acceptable ? Also consider that NULL returns different results than an empty string in queries. Will this affect smth? Are we sure we should prefer NULL?

[artur-intech]

@virtualfeline This is the default behaviour of Rails, if by missing value you mean empty string. I bet 99% of text columns in our database are empty if no value has been provided at the application level. I don't see production data so I cannot say how many of them are empty.

Also consider that NULL returns different results than an empty string in queries

Exactly! I am sure I want to see NULL when the data is missing or unknown, since this is the point of NULL. But is it worth the effort or not, is the topic of this ticket. If we leave it as is:

• It makes application code more complex
• Every time you do some string comparison (which is a lot of cases), you have to remember the fact that you need to include empty string check in addition to IS[NOT] NULL. This is error-prone. It means the data you get will be inaccurate. It applies to both application and database levels.

[artur-intech]

Example: #746 introduced contact reuse, so if one contact has fax value of "", and another of NULL, they will not be considered identical, and therefore will not be reused. @vohmar FYI

[artur-intech]

• https://stackoverflow.com/questions/7202319/rails-force-empty-string-to-null-in-the-database
• https://www.postgresql.org/message-id/AANLkTilEsUTIeUkZCX9Vc14kciFiuvFBRRE-yen4K_Zi@mail.gmail.com
• https://softwareengineering.stackexchange.com/questions/32578/sql-empty-string-vs-null-value