Difference between NULL and empty text

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 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.

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.

Method 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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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

Leave a Reply