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

--https://stackoverflow.com/q/78764855/5298879
create table if not exists t(A1,A2,ID)as values
 ('1,3,6,9,11','3,9',1)
,('2,3,5,7','3',2)
,('7,7,7,8,8','7',3)
,('7,8',null,4)
,(null,null,5)
,(null,'3,3',6)
,('1,2,null,4','2,null',7)
;
--I want to add a column with the result:
/*
A3
1,6,11
2,5,7
...
*/

SELECT 7


--https://www.postgresql.org/docs/current/intarray.html
create extension if not exists intarray;

select id
      ,a1
      ,a2
      ,string_to_array(a1,',')::int[] 
       - coalesce(string_to_array(a2,',')::int[],'{}') 
       as a3
from t
where id <> 7--intarray doesn't allow nulls in its arrays
;

CREATE EXTENSION




id
a1
a2
a3


1
1,3,6,9,11
3,9
{1,6,11}

2
2,3,5,7
3
{2,5,7}

3
7,7,7,8,8
7
{8}

4
7,8
null
{7,8}

5
null
null
null

6
null
3,3
null



SELECT 6


select id
      ,a1
      ,a2
      ,(select array_agg(e order by n) 
        from string_to_table(a1,',')with ordinality as a1_elements(e,n)
        where array_position(string_to_array(a2,','),e) is null) as a3
from t;


id
a1
a2
a3


1
1,3,6,9,11
3,9
{1,6,11}

2
2,3,5,7
3
{2,5,7}

3
7,7,7,8,8
7
{8,8}

4
7,8
null
{7,8}

5
null
null
null

6
null
3,3
null

7
1,2,null,4
2,null
{1,4}



SELECT 7


select id
      ,a1
      ,a2
      ,(select array_agg(e) 
        from (select string_to_table(a1,',') as e 
              except --a "very ansii" except syntax
              select string_to_table(a2,','))_) as a3
from t;


id
a1
a2
a3


1
1,3,6,9,11
3,9
{6,11,1}

2
2,3,5,7
3
{2,5,7}

3
7,7,7,8,8
7
{8}

4
7,8
null
{8,7}

5
null
null
null

6
null
3,3
null

7
1,2,null,4
2,null
{4,1}



SELECT 7


select id
      ,a1
      ,a2
      ,(select array_agg(e order by a1_elements.n) 
        from string_to_table(a1,',')with ordinality as a1_elements(e,n)
        left join string_to_table(a2,',')with ordinality as a2_elements(e,n) 
        using(e)where a2_elements.e is null)--anti-join
from t;


id
a1
a2
array_agg


1
1,3,6,9,11
3,9
{1,6,11}

2
2,3,5,7
3
{2,5,7}

3
7,7,7,8,8
7
{8,8}

4
7,8
null
{7,8}

5
null
null
null

6
null
3,3
null

7
1,2,null,4
2,null
{1,4}



SELECT 7

id	a1	a2	a3
1	1,3,6,9,11	3,9	{1,6,11}
2	2,3,5,7	3	{2,5,7}
3	7,7,7,8,8	7	{8}
4	7,8	null	{7,8}
5	null	null	null
6	null	3,3	null

id	a1	a2	a3
1	1,3,6,9,11	3,9	{6,11,1}
2	2,3,5,7	3	{2,5,7}
3	7,7,7,8,8	7	{8}
4	7,8	null	{8,7}
5	null	null	null
6	null	3,3	null
7	1,2,null,4	2,null	{4,1}