Postgres 9.4

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?.
create schema mgmt;
create table mgmt.log_metrics(id,object_references,dependent_object_references)as values
 (1,array['a','b','c'],array['d','e','f'])
,(2,array['g','h','i'],'{j,k,l}')
;
select id,unnest(array_cat(a.object_references, a.dependent_object_references)) 
from mgmt.log_metrics a


id
unnest


1
a

1
b

1
c

1
d

1
e

1
f

2
g

2
h

2
i

2
j

2
k

2
l



SELECT 12


select id,arr.element
from mgmt.log_metrics as a
cross join lateral unnest(array_cat( a.object_references
                                    ,a.dependent_object_references)) as arr(element);


id
element


1
a

1
b

1
c

1
d

1
e

1
f

2
g

2
h

2
i

2
j

2
k

2
l



SELECT 12


select id,arr.element
from mgmt.log_metrics as a
cross join lateral unnest(array_cat( a.object_references
                                    ,a.dependent_object_references)) as arr(element)
where arr.element<>all('{b,h,k}'::char[]);


id
element


1
a

1
c

1
d

1
e

1
f

2
g

2
i

2
j

2
l



SELECT 9


select id, unnest(array_remove( array_cat( a.object_references
                                          ,a.dependent_object_references)
                               ,'b'))
from mgmt.log_metrics as a;


id
unnest


1
a

1
c

1
d

1
e

1
f

2
g

2
h

2
i

2
j

2
k

2
l



SELECT 11


select a.id, b.arr
from mgmt.log_metrics as a
cross join lateral array_cat( a.object_references
                             ,a.dependent_object_references) as b(arr)
where b.arr @> array['k'];


id
arr


2
{g,h,i,j,k,l}



SELECT 1


select a.id, b.arr
from mgmt.log_metrics as a
cross join lateral array_cat( a.object_references
                             ,a.dependent_object_references) as b(arr)
where b.arr[array_length(b.arr,1)] = 'l';


id
arr


2
{g,h,i,j,k,l}



SELECT 1


select a.id, u.position, u.element
from mgmt.log_metrics as a
cross join lateral array_cat( a.object_references
                             ,a.dependent_object_references) as b(arr)
cross join lateral unnest(b.arr) WITH ORDINALITY as u(element,position)
where u.element='l' 
  and u.position=array_length(b.arr,1);


id
position
element


2
6
l



SELECT 1


--PostgreSQL 9.5+
select a.id, b.arr
from mgmt.log_metrics as a
cross join lateral array_cat( a.object_references
                             ,a.dependent_object_references) as b(arr)
where array_position(b.arr,'k',1) is not null;

ERROR:  function array_position(text[], unknown, integer) does not exist
LINE 6: where array_position(b.arr,'k',1) is not null;
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.