Best way to check for "empty or null value"
The expression stringexpression = ''
yields:
TRUE
.. for ''
(or for any string consisting of only spaces with the data type char(n)
)NULL
.. for NULL
FALSE
.. for anything else
So to check for: "stringexpression
is either NULL or empty":
(stringexpression = '') IS NOT FALSE
Or the reverse approach (may be easier to read):
(stringexpression <> '') IS NOT TRUE
Works for any character type including char(n)
. The manual about comparison operators.
Or use your original expression without trim()
, which is costly noise for char(n)
(see below), or incorrect for other character types: strings consisting of only spaces would pass as empty string.
coalesce(stringexpression, '') = ''
But the expressions at the top are faster.
Asserting the opposite is even simpler: "stringexpression
is neither NULL nor empty":
stringexpression <> ''
About char(n)
This is about the data type char(n)
, short for: character(n)
. (char
/ character
are short for char(1)
/ character(1)
.) Its use is discouraged in Postgres:
In most situations
text
orcharacter varying
should be used instead.
Do not confuse char(n)
with other, useful, character types varchar(n)
, varchar
, text
or "char"
(with double-quotes).
In char(n)
an empty string is not different from any other string consisting of only spaces. All of these are folded to n spaces in char(n)
per definition of the type. It follows logically that the above expressions work for char(n)
as well - just as much as these (which wouldn't work for other character types):
coalesce(stringexpression, ' ') = ' 'coalesce(stringexpression, '') = ' '
Demo
Empty string equals any string of spaces when cast to char(n)
:
SELECT ''::char(5) = ''::char(5) AS eq1 , ''::char(5) = ' '::char(5) AS eq2 , ''::char(5) = ' '::char(5) AS eq3;
Result:
eq1 | eq2 | eq3 ----+-----+---- t | t | t
Test for "null or empty string" with char(n)
:
SELECT stringexpression , stringexpression = '' AS base_test , (stringexpression = '') IS NOT FALSE AS test1 , (stringexpression <> '') IS NOT TRUE AS test2 , coalesce(stringexpression, '') = '' AS coalesce1 , coalesce(stringexpression, ' ') = ' ' AS coalesce2 , coalesce(stringexpression, '') = ' ' AS coalesce3FROM ( VALUES ('foo'::char(5)) , ('') , (' ') -- not different from '' in char(n) , (NULL) ) sub(stringexpression);
Result:
stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3 ------------------+-----------+-------+-------+-----------+-----------+----------- foo | f | f | f | f | f | f | t | t | t | t | t | t | t | t | t | t | t | t null | null | t | t | t | t | t
Test for "null or empty string" with text
:
SELECT stringexpression , stringexpression = '' AS base_test , (stringexpression = '') IS NOT FALSE AS test1 , (stringexpression <> '') IS NOT TRUE AS test2 , coalesce(stringexpression, '') = '' AS coalesce1 , coalesce(stringexpression, ' ') = ' ' AS coalesce2 , coalesce(stringexpression, '') = ' ' AS coalesce3FROM ( VALUES ('foo'::text) , ('') , (' ') -- different from '' in a sane character types , (NULL) ) sub(stringexpression);
Result:
stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3 ------------------+-----------+-------+-------+-----------+-----------+----------- foo | f | f | f | f | f | f | t | t | t | t | f | f | f | f | f | f | f | f null | null | t | t | t | t | f
Related:
To check for null and empty:
coalesce(string, '') = ''
To check for null, empty and spaces (trim the string)
coalesce(TRIM(string), '') = ''
Checking for the length of the string also works and is compact:
where length(stringexpression) > 0;