I have two columns ind and tar that both contain arrays.

 ind      tar
{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}

I want to find if one value exists in both arrays, and if that’s true, I want to keep it only at column ind. For example, at the first row I have the value 10 in both columns. I want to end up with this value only in column ind and leave column tar empty. This is the expected result:

 ind      tar      
{10}      
{6}       {5}
{4,5,6}   
{5,6}     
{7,8}     {11}
{11}      {5,6,7}
{11}      {8}
{9,10}    {6}

How can I do that in PostgreSQL?

So far I only managed to find the common elements, but I don’t know how to continue with keeping them only at ind column and remove them from tar column.

with t1 as (
select distinct ind, tar
from table_1
join table_2 using (id) 
limit 50
),
t2 as (
select ind & tar as common_el, ind , tar 
from t1
)
select *
from t2

which results into this:

   common_el   ind        tar
    {10}      {10}       {10}
    {6}       {6}       {5,6}
    {5,6}     {4,5,6}   {5,6}
    {5,6}     {5,6}     {5,6}

>Solution :

The & operator you are using is from the intarray module which also allows you to use - to remove elements in one array from another.

For eg.

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

View working demo db fiddle here