Reputation: 27919

Best way to check for "empty or null value"

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?

Upvotes: 343

Answers (11)

Erwin Brandstetter

Reputation: 656361

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

"`stringexpression` is either null or empty"

To check for this, use:

(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 would be 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.

"`stringexpression` is neither null nor empty"

Asserting the opposite is simpler:

stringexpression <> ''

Either way, document your exact intention in an added comment if there is room for ambiguity.

About `char(n)`

The data type char(n) is short for character(n).
char / character are short for char(1) / character(1).
bpchar is an internal alias of character. (Think "blank-padded character".)
This data type is supported for historical reasons and for compatibility with the SQL standard, but 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?

Upvotes: 524

noonex

Reputation: 2075

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

Upvotes: 1

Ethan Allen

Reputation: 11

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.

Upvotes: 1

John VE

Reputation: 181

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.

Upvotes: 17

Mowazzem Hosen

Reputation: 507

another way is

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

Upvotes: 12

yglodt

Reputation: 14551

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

where length(stringexpression) > 0;

Upvotes: 39

TarasB

Reputation: 2428

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.

Upvotes: 2

Ammy

Reputation: 379

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)

Upvotes: 0

user4010677

Reputation:

To check for null and empty:

coalesce(string, '') = ''

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

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

Upvotes: 122

Danilo da Silva

Reputation: 11

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.

Upvotes: 0

Świstak35

Reputation: 350

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

Upvotes: 2

Best way to check for &quot;empty or null value&quot;

Answers (11)

"stringexpression is either null or empty"

"stringexpression is neither null nor empty"

About char(n)

Demo

Related Questions

Best way to check for "empty or null value"

"`stringexpression` is either null or empty"

"`stringexpression` is neither null nor empty"

About `char(n)`