PostgreSQL : check if value is in 2 columns and remove it from one of them

Question

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}

Answer 1

You can do it this way (fiddle):

Table creation:

CREATE TABLE t(x INTEGER[], y INTEGER[]);

Populate the table:

INSERT INTO t 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}'),

--
-- records below added for testing!
--

('{11}',      '{5,8,10,11,133}'),  
('{9,10}',    '{4,5,6,8,9,10,11}'),
('{9,10}',    '{4,5,6,8,9,10,11}');

Standard ARRAY, UNNEST + EXCEPT (fiddle):

If you don't want to, or can't, use INTARRAY.

SELECT 
  t.x,
  ARRAY((SELECT UNNEST(t.y)) EXCEPT (SELECT UNNEST(t.x))) 
FROM 
  t;

Result:

x   array
{10}    {}
{6} {5}
{4,5,6} {}
{5,6}   {}
{7,8}   {11}
{11}    {7,5,6}
{11}    {8}
{9,10}  {6}
{11}    {8,10,133,5}
{9,10}  {11,8,5,4,6}
{9,10}  {11,8,5,4,6}

Et voilà - the desired result! See here for an excellent thread with many approaches to this and closely related issues discussed!

Answer 2

This can also be done like this (all the code below is available on the fiddle here):

CREATE OR REPLACE FUNCTION array_diff (array1 ANYARRAY, array2 ANYARRAY)
RETURNS ANYARRAY 
AS $$

  SELECT COALESCE(ARRAY_AGG(elem), '{}')
  FROM 
    UNNEST(array1) elem
  WHERE elem <> ALL (array2)

$$ LANGUAGE SQL STRICT IMMUTABLE;

and to use it (I put extra records in for testing - check the fiddle):

SELECT
  ROW_NUMBER() OVER (ORDER BY NULL) rn,
  x, y,
  array_diff(y, x)
FROM t
ORDER BY rn;

Result:

rn        x                 y     array_diff
1      {10}              {10}             {}
2       {6}             {5,6}            {5}
3   {4,5,6}             {5,6}             {}
4     {5,6}             {5,6}             {}
5     {7,8}              {11}           {11}
6      {11}           {5,6,7}        {5,6,7}
7      {11}               {8}            {8}
8    {9,10}               {6}            {6}
9      {11}   {5,8,10,11,133}   {5,8,10,133}
10   {9,10} {4,5,6,8,9,10,11}   {4,5,6,8,11}
11   {9,10} {4,5,6,8,9,10,11}   {4,5,6,8,11}

See the fiddle for the (trivial) UPDATE.

Answer 3

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

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}

or simpler

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

View working demo db fiddle here

Edit 1: For non intarray module users.

Using UNNEST to transform the array into multiple rows this can be solved with multiple sql approaches to identify where elements of 1 set is not in another eg.

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}

View working demo on db fiddle