Postgres 16

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?.

select 'abc'=any('{abc,def,ghi}'::text[]);


?column?


t



SELECT 1


select 'abc'=any('{}'::text[]);


?column?


f



SELECT 1


select tags_arg,
       tags_arg::text[] as_array,
       'tag1'=any(tags_arg::text[])                                 as "base",
       'tag1'=any(coalesce(tags_arg::text[],array['tag1']::text[])) as "+coalesce()",
       'tag1'=any(coalesce(tags_arg::text[],array['tag1']::text[]))
            OR array_length(tags_arg::text[],1) is null             as "+coalesce()+OR",
       'tag1'=any(coalesce(tags_arg::text[],array['tag1']::text[]))
            OR array_length(tags_arg::text[],1) is null
            OR array_position(tags_arg::text[],null) is not null    as "+coalesce()+OR+nullsafe",
       array_position(tags_arg::text[],'tag1') is not null 
            OR array_length(tags_arg::text[],1) is null             as "final"
from (values  ('{tag1,tag2,tag3}')
             ,('{tag4,tag5}')
             ,('{tag6,null}')
             ,('{null}')
             ,('{}')
             ,(null)              ) as args(tags_arg);


tags_arg
as_array
base
+coalesce()
+coalesce()+OR
+coalesce()+OR+nullsafe
final


{tag1,tag2,tag3}
{tag1,tag2,tag3}
t
t
t
t
t

{tag4,tag5}
{tag4,tag5}
f
f
f
f
f

{tag6,null}
{tag6,NULL}
null
null
null
t
f

{null}
{NULL}
null
null
null
t
f

{}
{}
f
f
t
t
t

null
null
null
t
t
t
t



SELECT 6

tags_arg	as_array	base	+coalesce()	+coalesce()+OR	+coalesce()+OR+nullsafe	final
{tag1,tag2,tag3}	{tag1,tag2,tag3}	t	t	t	t	t
{tag4,tag5}	{tag4,tag5}	f	f	f	f	f
{tag6,null}	{tag6,NULL}	null	null	null	t	f
{null}	{NULL}	null	null	null	t	f
{}	{}	f	f	t	t	t
null	null	null	t	t	t	t