Best way to check for "empty or null value"

Question

What is best way to check if value is null or empty string in Postgres sql statements?

Value can be long expression so it is preferable that it is written only once in check.

Currently I'm using:

coalesce( trim(stringexpression),'')=''

But it looks a bit ugly.

stringexpression may be char(n) column or expression containing char(n) columns with trailing spaces.

What is best way?

Using `char` is almost always the wrong choice due to padding (and the resulting space waste). But apart from that: I don't think there is a better solution. — , May 20 '14 at 17:30

Erwin Brandstetter · Accepted Answer · 2023-06-13T21:44:28.487

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 or character 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 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

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 coalesce3
FROM  (
   VALUES
     ('foo'::text)
   , ('')
   , ('   ')                -- different from '' in 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}

Any downsides of using data type "text" for storing strings?

Maybe I'm missing something, but it doesn't check for leading or trailing whitespace. — , May 20 '14 at 19:12
@a_horse_with_no_name: OP asks for the `best way to check if value is null or empty string`. The `trim()` call is (comparatively) expensive - and just not necessary. I added more about `char(n)` and "empty string". — Erwin Brandstetter, May 20 '14 at 19:42
You wrote that any string expression containing only spaces is equal to `''` . Can I remove trim and use `coalesce(stringexpression,'')=''` to check. This looks more readable to me compared to your answer. — Andrus, May 21 '14 at 14:08
@Andrus: Yes, you can. I added that and some more to the answer. — Erwin Brandstetter, May 21 '14 at 14:54
`select coalesce(' ', '') = ''` returns false. So TRIM() is required — Andrus, May 21 '14 at 18:17
But `coalesce(' '::char(5), '') = ''` does not. I would use one of the top two expressions in any case, which work for any character type and are fastest and cleanest. — Erwin Brandstetter, May 21 '14 at 18:58
The fastest queries you mention are not working on Oracle. This query will work on oracle and postgres : WHERE NOT(stringexpression <> ''); — Woody, Sep 06 '17 at 14:10
@Woody: It will return NULL for NULL input. That's *not* what the question is asking for. This answer is for Postgres, not Oracle, where NULL and the empty string are considered the same - in violation of the SQL standard. — Erwin Brandstetter, Sep 06 '17 at 14:19
The expression "Or the reverse approach (may be easier to read): (stringexpression <> '') IS NOT TRUE" is not true. — ayhan, Jun 30 '21 at 09:14
Oh, I misunderstood that line. By saying "reverse approach", I thought "stringexpression is neither NULL nor empty" is meant, but I see that another way of expressing "stringexpression is either NULL or empty" is shown at that line. So that expression is correct. Sorry. — ayhan, Jun 30 '21 at 11:36
Using `(stringexpression = '') IS NOT FALSE` is a bad advice because of poor readablity. It may be confusing for the reader as to what happens when stringexpression is NULL. Using coalesce fot this is a much better idea. Readability is **important**. If someone misunderstands your code it may result in a bug. A lot of code is read by multiple people, with varying experience, over multiple years. — godfryd, Mar 18 '22 at 13:51
Google brought me here even though I was looking for the opposite case: `Not null Nor empty`... I wonder if you can add it as an fyi to complete the very nice answer... *EDIT*: well, it should be `(stringexpression = '') IS FALSE` xD — Enissay, Jun 10 '22 at 00:48
@Enissay: Simply `stringexpression <> ''` for *"Not null Nor empty"*. That's already added in above. — Erwin Brandstetter, Jun 10 '22 at 01:16
Using "Postgres 11" and only original version with "coalesce" and "trim" (from question) works for space-filled strings for me: `coalesce( trim(stringexpression),'')=''` — Rib47, Jun 21 '22 at 08:00

score 110 · Answer 2 · edited Dec 14 '16 at 10:59

110

To check for null and empty:

coalesce(string, '') = ''

To check for null, empty and spaces (trim the string)

coalesce(TRIM(string), '') = ''

edited Dec 14 '16 at 10:59

Amgad Fahmi

4,349
3
19
18

answered Dec 14 '16 at 10:30

btw, `coalesce(a, ...b)` returns the first non-null value (https://www.postgresql.org/docs/8.1/functions-conditional.html) – bendytree Jun 21 '21 at 20:46

score 36 · Answer 3 · answered Apr 07 '19 at 19:37

36

Checking for the length of the string also works and is compact:

where length(stringexpression) > 0;

answered Apr 07 '19 at 19:37

yglodt

13,807
14
91
127

2

Did you check this for the NULL case? – Flinsch Jan 14 '20 at 09:45
2

Yes, I did. It does not return empty nor null string fields. – yglodt Jan 14 '20 at 11:58
1

If you just need to check only empty values, then try this -> `where length(stringexpression) = 0;`. This works for me. – Kushan Gunasekera Jun 05 '20 at 12:15
I like this solution, just note that is some cases calculating exact length may be relatively expensive operation. – noonex Jul 28 '21 at 12:01
In postgres, `length(NULL) > 0` return `NULL`, not `False`. – Automatico Sep 19 '22 at 12:43
I tested this in Postico's SQL Query interface, and it does not work for `NULL`s. – therealrodk Feb 20 '23 at 16:47

score 12 · Answer 4 · answered Nov 27 '19 at 16:50

12

A lot of the answers are the shortest way, not the necessarily the best way if the column has lots of nulls. Breaking the checks up allows the optimizer to evaluate the check faster as it doesn't have to do work on the other condition.

(stringexpression IS NOT NULL AND trim(stringexpression) != '')

The string comparison doesn't need to be evaluated since the first condition is false.

answered Nov 27 '19 at 16:50

John VE

131
1
3

Thanks! This works, and is explicit code, which improves readability. – therealrodk Feb 20 '23 at 16:52

score 11 · Answer 5 · answered Sep 12 '19 at 02:43

11

another way is

nullif(trim(stringExpression),'') is not null

answered Sep 12 '19 at 02:43

Mowazzem Hosen

457
4
10

score 2 · Answer 6 · answered May 20 '14 at 17:39

2

If there may be empty trailing spaces, probably there isn't better solution. COALESCE is just for problems like yours.

answered May 20 '14 at 17:39

Świstak35

350
2
12

score 2 · Answer 7 · answered Feb 05 '18 at 18:30

2

Something that I saw people using is stringexpression > ''. This may be not the fastest one, but happens to be one of the shortest.

Tried it on MS SQL as well as on PostgreSQL.

answered Feb 05 '18 at 18:30

TarasB

2,407
1
24
32

score 1 · Answer 8 · answered Jun 25 '21 at 16:18

found this post looking for a solution to 'don't show me data that is '' (blank or single space char) or null'. in my case, we only want to show the user records with these values populated. i hope this response helps another looking for the same. the answers above didn't work in my case.

our app is running rails with postgres. looking at how rails builds the query for .where.not(company_website: [nil, '']) in our app, which works just fine, i can see the resulting sql statement in console.

WHERE NOT ((contacts.company_website = '' OR contacts.company_website IS NULL))

i added this bit and it works as intended.

score 1 · Answer 9 · answered Jul 28 '21 at 12:46

1

I like answer by yglodt, but calculating exact length may be expensive for big sets and big strings, so I go with:

coalesce(trim('a') > '','f')

answered Jul 28 '21 at 12:46

noonex

1,975
1
16
18

Danilo da Silva · Answer 10 · 2016-08-04T12:08:50.543

0

My preffered way to compare nullable fields is: NULLIF(nullablefield, :ParameterValue) IS NULL AND NULLIF(:ParameterValue, nullablefield) IS NULL . This is cumbersome but is of universal use while Coalesce is impossible in some cases.

The second and inverse use of NULLIF is because "NULLIF(nullablefield, :ParameterValue) IS NULL" will always return "true" if the first parameter is null.

edited Aug 04 '16 at 12:08

answered Aug 04 '16 at 12:00

Danilo da Silva

11
3

score 0 · Answer 11 · answered Feb 20 '17 at 15:12

0

If database having large number of records then null check can take more time you can use null check in different ways like : 1) where columnname is null 2) where not exists() 3) WHERE (case when columnname is null then true end)

answered Feb 20 '17 at 15:12

Ammy

369
2
8

Best way to check for "empty or null value"

11 Answers11

About `char(n)`

Demo

Linked

Related

Best way to check for "empty or null value"

11 Answers11

About char(n)

Demo

Linked

Related

About `char(n)`