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:
- A2 will never have values that are not in A1.
- 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.
user26413110 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
5
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(coalesce(a2, '{}'))
group by id order by id;
| a | id |
|---|---|
| 1,6,11 | 1 |
| 2,5,7 | 2 |
DB Fiddle demo
3
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. To keep those:
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)
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} |
- Unpacking, comparing and re-aggregating the arrays takes place in a scalar subquery operating within the context of an individual row.
- The
with ordinalitytells you the original positions of each element, whicharray_agg()uses to restore the original order when it packs them back up. string_to_table()does the job ofunnest(string_to_array())in one call.array_position()checks for elements from a1 in a2. Unlike=any()orin (), it usesis distinct frominstead of plain equality; if you had somenullelements ina1that are listed ina2for removal, it can handle it.
The demo also shows examples with except and an anti-join.
1