All we need is an easy explanation of the problem, so here it is.
I’m using Postgres. Is there any significant difference in storage or performance between using
val text NULL CHECK(val <> '') (or
val text NOT NULL? The text can’t be empty. Hence, the empty text could serve as marker for not set. But does it make any difference according to storage for example?
I know, I lose the NULL magic with the empty text, but this doesn’t matter. On the other side I gain a much easier import into the application, because I don’t have to handle
NULL. It’s only string in, string out.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
There is no performance difference between
Your first column definition does not make much sense: Either represent missing values as empty strings or as NULLs, so either go with
val text NOT NULL
val text CHECK (val <> '')
An empty string needs slightly more storage than a NULL value, but that should not be the deciding factor.
My recommendation is that you make the decision based on what the meaning of a missing or empty string is:
if you want to express "I know that this value is empty", then use the empty string
if you want to express "I don’t know what the value is, or this attribute has no meaning in this row", go with NULL
But don’t make the decision without considering the queries. If the query is "how many rows have that property set", using NULL makes more sense, because you could query like this:
SELECT count(attribute) FROM tab;
SELECT count(*) FILTER (WHERE attribute <> '') FROM tab;
On the other hand, if you want to know which rows have the attribute set to "xyz" or not set at all, the empty string would be better:
SELECT count(*) FROM tab WHERE attribute IN ('xyz, '');
SELECT count(*) FROM tab WHERE attribute = 'xyz' OR attribute IS NULL;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂