Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE table_1 (
  ind int[],
  tar int[]
);

INSERT INTO table_1
  (ind, tar)
VALUES
  ('{10}', '{10}'),
  ('{6}', '{5,6}'),
  ('{4,5,6}', '{5,6}'),
  ('{5,6}', '{5,6}'),
  ('{7,8}', '{11}'),
  ('{11}', '{5,6,7}'),
  ('{11}', '{8}'),
  ('{9,10}', '{6}');
8 rows affected

select
    ind,
    tar,
    ind & tar as common_el,
    tar - (ind & tar) as  new_tar
from
    table_1


ind
tar
common_el
new_tar


{10}
{10}
{10}
{}

{6}
{5,6}
{6}
{5}

{4,5,6}
{5,6}
{5,6}
{}

{5,6}
{5,6}
{5,6}
{}

{7,8}
{11}
{}
{11}

{11}
{5,6,7}
{}
{5,6,7}

{11}
{8}
{}
{8}

{9,10}
{6}
{}
{6}


select
    ind,
    tar,
    ind & tar as common_el,
    tar - ind as  new_tar
from
    table_1


ind
tar
common_el
new_tar


{10}
{10}
{10}
{}

{6}
{5,6}
{6}
{5}

{4,5,6}
{5,6}
{5,6}
{}

{5,6}
{5,6}
{5,6}
{}

{7,8}
{11}
{}
{11}

{11}
{5,6,7}
{}
{5,6,7}

{11}
{8}
{}
{8}

{9,10}
{6}
{}
{6}


select
    ind,
    tar,
    array(
        select t1.val from unnest(tar) t1(val)
        inner join unnest(ind) i1(val) on i1.val=t1.val
    ) as common_el,
    array(
        select t1.val from unnest(tar) t1(val)
        where t1.val not in (
             select val from unnest(ind) i1(val)
        )
    ) as  new_tar
from
    table_1


ind
tar
common_el
new_tar


{10}
{10}
{10}
{}

{6}
{5,6}
{6}
{5}

{4,5,6}
{5,6}
{5,6}
{}

{5,6}
{5,6}
{5,6}
{}

{7,8}
{11}
{}
{11}

{11}
{5,6,7}
{}
{5,6,7}

{11}
{8}
{}
{8}

{9,10}
{6}
{}
{6}


select
    ind,
    array(
        select t1.val from unnest(tar) t1(val)
        where t1.val not in (
             select val from unnest(ind) i1(val)
        )
    ) as  new_tar
from
    table_1


ind
new_tar


{10}
{}

{6}
{5}

{4,5,6}
{}

{5,6}
{}

{7,8}
{11}

{11}
{5,6,7}

{11}
{8}

{9,10}
{6}


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
select
    ind,
    array(
        select t1.val from unnest(tar) t1(val)
        where t1.val not in (
             select val from unnest(ind) i1(val)
        )
    ) as  new_tar
from
    table_1


QUERY PLAN


Seq Scan on public.table_1  (cost=0.00..243.20 rows=880 width=64) (actual time=0.042..0.075 rows=8 loops=1)

  Output: table_1.ind, (SubPlan 2)

  Buffers: shared hit=1

  SubPlan 2

    ->  Function Scan on pg_catalog.unnest t1  (cost=0.13..0.26 rows=5 width=4) (actual time=0.005..0.006 rows=1 loops=8)

          Output: t1.val

          Function Call: unnest(table_1.tar)

          Filter: (NOT (hashed SubPlan 1))

          Rows Removed by Filter: 1

          SubPlan 1

            ->  Function Scan on pg_catalog.unnest i1  (cost=0.00..0.10 rows=10 width=4) (actual time=0.001..0.001 rows=2 loops=8)

                  Output: i1.val

                  Function Call: unnest(table_1.ind)

Planning Time: 0.067 ms

Execution Time: 0.119 ms


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
select
    ind,
    tar - ind as  new_tar
from
    table_1


QUERY PLAN


Seq Scan on public.table_1  (cost=0.00..21.00 rows=880 width=64) (actual time=0.010..0.014 rows=8 loops=1)

  Output: ind, (tar - ind)

  Buffers: shared hit=1

Planning Time: 0.023 ms

Execution Time: 0.025 ms
ind	tar	common_el	new_tar
{10}	{10}	{10}	{}
{6}	{5,6}	{6}	{5}
{4,5,6}	{5,6}	{5,6}	{}
{5,6}	{5,6}	{5,6}	{}
{7,8}	{11}	{}	{11}
{11}	{5,6,7}	{}	{5,6,7}
{11}	{8}	{}	{8}
{9,10}	{6}	{}	{6}