Problem
In Postgres sql statements, what is the best technique to check if a value is null or empty string?
Because value can be a long expression, it’s best if it’s just written once in check.
Currently I’m using:
coalesce( trim(stringexpression),'')=''
However, it appears to be somewhat unsightly.
A char(n) column or an expression including char(n) columns with trailing spaces can be used as a stringexpression.
What is the most effective method?
Asked by Andrus
Solution #1
The expression stringexpression = ” produces the following results:
TRUE.. for ” (or any string with the data type char(n) containing only spaces) NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ANYTHING ELSE IS FALSE.
To test whether “stringexpression is either NULL or empty,” do the following:
(stringexpression = '') IS NOT FALSE
Alternatively (and perhaps more easily read):
(stringexpression <> '') IS NOT TRUE
Any character type, including char, can be used (n). The comparative operators’ manual.
Alternatively, you can use your original expression without trim(), which is inefficient for char(n) (see below) and erroneous for other character types: Only spaces in a string would be treated as an empty string.
coalesce(stringexpression, '') = ''
The expressions near the top, on the other hand, are speedier.
It’s considerably easier to prove the opposite: “stringexpression is neither NULL nor empty”:
stringexpression <> ''
This is about the char(n) data type, which stands for character (n). (Char and character are abbreviations for char(1) and character(1), respectively.) In Postgres, its use is discouraged:
Do not confuse char(n) with the useful varchar(n), varchar, text, or “char” character types (with double-quotes).
In char(n), an empty string is the same as any other string made up entirely of spaces. According to the type’s definition, all of these are folded to n spaces in char(n). As a result, the following expressions work for char(n) just as well as these (which wouldn’t work for other character types):
coalesce(stringexpression, ' ') = ' '
coalesce(stringexpression, '') = ' '
When cast to char(n), an empty string equals any string of spaces:
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
char(n) checks for “null or empty string”:
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 coalesce3
FROM (
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
With text, check for “null or empty string”:
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 coalesce3
FROM (
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
db<>fiddle here Old sqlfiddle
Related:
Answered by Erwin Brandstetter
Solution #2
To check for null and empty values, type:
coalesce(string, '') = ''
To look for nulls, empty spaces, and spaces (trim the string)
coalesce(TRIM(string), '') = ''
Answered by sam
Solution #3
Checking for the string’s length is also a viable option and is more compact:
where length(stringexpression) > 0;
Answered by yglodt
Solution #4
another way is
nullif(trim(stringExpression),'') is not null
Answered by Mowazzem Hosen
Solution #5
A lot of the answers are the shortest way, not the necessarily the best way if the column has lots of nulls. Because the optimizer doesn’t have to work on the other condition, breaking the checks up helps it to analyze the check faster.
(stringexpression IS NOT NULL AND trim(stringexpression) != '')
The string comparison doesn’t need to be evaluated since the first condition is false.
Answered by John VE
Post is based on https://stackoverflow.com/questions/23766084/best-way-to-check-for-empty-or-null-value