Postgres 9.6

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