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

"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}

Related:

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