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.
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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;
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0