TRUE .. for '' (or for any string consisting of only spaces with the data type char(n))
NULL .. for NULL
FALSE .. for anythinganything else
Works for any character type including the obsolescent char(n) which is hardly ever useful.
The manual about comparison operators.
OrOr use theyour original expression you already had, just without the trim()trim(), which would be uselessis costly noise for char(n) (see below), or it would include strings consisting of only spaces in the testincorrect for other character types: strings consisting of only spaces would pass as empty string.
Asserting the opposite is even simpler: "stringexpression is neither NULL nor empty" is even simpler:
###About char(n)
Do not confuse this data type with other character types like varchar(n), varchar, text or "char" (with quotes), which are all useful data types. This is about the outdated data type with very limited usefulness: char(n), short for: character(n). Also, (char and/ 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" (same thingwith double-quotes).
In char(n) (unlike other string types!) 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 this worksthe above expressions work for char(n) as well:
coalesce(stringexpression, '') = ''
Just - just as much as these (which wouldn't work for other character types):
###Demo
Demo
SELECT ''::char(5) = ''::char(5) AS eq1
, ''::char(5) = ' '::char(5) AS eq2
, ''::char(5) = ' '::char(5) AS eq3;
eq1 | eq2 | eq3
----+-----+----
t | t | t
Result:
eq1 | eq2 | eq3
----+-----+----
t | t | t
SELECT stringexpression
, stringexpression = '' AS simple_testbase_test
, (stringexpression = '') IS NOT FALSE AS test1
, (stringexpression <> '') IS NOT TRUE AS test2
, coalesce(stringexpression, '') = '' AS test_coalesce1coalesce1
, coalesce(stringexpression, ' ') = ' ' AS test_coalesce2coalesce2
, coalesce(stringexpression, '') = ' ' AS test_coalesce3coalesce3
FROM (
VALUES
('foo'::char(5))
, ('')
, (NULL)
, (' ') -- not different from '' in char(n)
, (NULL)
) sub(stringexpression);
stringexpression | simple_test | test1 | test2 | test_coalesce1 | test_coalesce2 | test_coalesce3
------------------+-------------+-------+-------+----------------+----------------+----------------
foo | f | f | f | f | f | f
| t | t | t | t | t | t
| | t | t | t | t | t
| t | t | t | t | t | t
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 simple_testbase_test
, (stringexpression = '') IS NOT FALSE AS test1
, (stringexpression <> '') IS NOT TRUE AS test2
, coalesce(stringexpression, '') = '' AS test_coalesce1coalesce1
, coalesce(stringexpression, ' ') = ' ' AS test_coalesce2coalesce2
, coalesce(stringexpression, '') = ' ' AS test_coalesce3coalesce3
FROM (
VALUES
('foo'::text)
, ('')
, (NULL)
, (' ') -- different from '' in a sane character typetypes
like text , (NULL)
) sub(stringexpression);
stringexpression | simple_test | test1 | test2 | test_coalesce1 | test_coalesce2 | test_coalesce3
------------------+-------------+-------+-------+----------------+----------------+----------------
foo | f | f | f | f | f | f
| t | t | t | t | f | f
| | t | t | t | t | f
| f | f | f | f | f | f
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
dbfiddle heredb<>fiddle here
Old Old SQL Fiddlesqlfiddle
