Difference between NULL and empty text

Question

I'm using Postgres. Is there any significant difference in storage or performance between using val text NULL CHECK(val <> '') (or varchar) and 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.

Answer 1

There is no performance difference between text and varchar.

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

or with

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;

rather than

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, '');

versus

SELECT count(*) FROM tab WHERE attribute = 'xyz' OR attribute IS NULL;