PostgreSQL Question posted in
The official documentation can be found here.

How can I, having two arrays, remove elements of the second array to the first array – PostgreSQL?

Let’s say I have a table t, 2 of its columns are A1 and A2. It looks something like this:

A1 A2 ID
1,3,6,9,11 3,9 1
2,3,5,7 3 2
…

I want to add a column with the result:

A3
1,6,11
2,5,7
…

The values are actually strings, but I’m assuming dealing with arrays is easier, and that STRING_TO_ARRAY and ARRAY_TO_STRING will work (beginning and ending).
We can assume that:

  1. A2 will never have values that are not in A1.
  2. A2 will never be equal to A1.
    Also, A2 can be null, but I am guessing that a CASE will solve this.

I know PostgreSQL has the function ARRAY_REMOVE(). So I tried unnesting, but then I have two different arrays, one without a ‘3’, another without a ‘9’ (first example). So I would like its intersection, but I also don’t know how to do that and whether it’s easier than my initial problem or if I’m just trying to solve a difficult problem (for me, at least) by trying to solve an even more difficult one.
I also tried something like this, that I kind of understand why it didn’t work:

SELECT ID, ARRAY_AGG(elem)
from t, UNNEST(STRING_TO_ARRAY(A1,',')) elem
where elem <> all(
SELECT UNNEST(STRING_TO_ARRAY(A2,',')) FROM t
)
GROUP BY ID

Probably worth mentioning that I am a couple weeks into learning SQL, so there might be ways to do things that I don’t know about.

Tags:

2

Answers


  1. 0

    You can string_to_array() first, like you planned to, then make that an int[] which you can subtract with a simple - from intarray extension.
    demo at db<>fiddle

    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;
    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

    Note that intarray’s - subtraction is set-based – it will deduplicate the inputs.

    Login or Signup to reply.
  2. 0

    Unnest/aggregate these array elements of A1 that do not exist in A2. Basically this is the same idea as in your query.
    the_table CTE is a mimic of real data, t CTE has the strings convered to arrays.

    with the_table(a1, a2, id) as (
 values
  ('1,3,6,9,11', '3,9', '1'),
  ('2,3,5,7', '3', '2')
),
t as (
 select string_to_array(a1, ',')::integer[] a1, 
        string_to_array(a2, ',')::integer[] a2, id
 from the_table
)
select array_agg(e) a, id 
from t cross join lateral unnest(a1) e
where not e = any(a2)
group by id order by id;
    a id
    1,6,11 1
    2,5,7 2

    DB Fiddle demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search