By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- 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;
| eq1 | eq2 | eq3 |
|---|---|---|
| t | t | t |
SELECT 1
-- 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);
| 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 |
SELECT 4
-- 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 a sane character type like text
, (NULL)
) sub(stringexpression);
| 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 |
SELECT 4