CODEWITHSUNDEEP
postgresql
Jim Miller
Jim Miller

Reputation: 105

Postgresql - Basic Arrays and array_agg

As a Test I created this schema:

CREATE TABLE simple_table (client_id int4, order_id int4);
INSERT INTO simple_table (client_id, order_id) 
VALUES 
(1,2),(1,3),(1,4),(1,6),(1,8),(1,12),(1,16),(1,18),(1,25),(1,32),(1,33),(1,37),(1,43),
(1,56),(1,57),(1,66),(2,2),(2,3),(2,5),(2,7),(2,9),(2,12),(2,17),(2,19),(2,22),(2,30),
(2,33),(2,38),(2,44),(2,56),(2,58),(2,66)
;



Then used array_agg:

SELECT client_id, array_agg(order_id) FROM simple_table GROUP BY client_id;



to create the arrays for client 1 and client 2:

| CLIENT_ID |                                  ARRAY_AGG |
----------------------------------------------------------
|         1 | 2,3,4,6,8,12,16,18,25,32,33,37,43,56,57,66 |
|         2 | 2,3,5,7,9,12,17,19,22,30,33,38,44,56,58,66 |



Now I would like to compare the 2 rows and identify the like values. Tried && overlap (have elements in common) ARRAY[1,4,3] && ARRAY[2,1] from the Postgresql documentation but I am having problems.

Perhaps I am looking at this wrong. Any help or guidance would be appreciated!

Upvotes: 1

Views: 1816

Answers (2)

bma
bma

Reputation: 9756

Take a look at the "array_intersect" functions here: Array Intersect

To see elements that are not common to both arrays:

create or replace function arrxor(anyarray,anyarray) returns anyarray as $$
select ARRAY(
        (
        select r.elements
        from    (
                (select 1,unnest($1))
                union all
                (select 2,unnest($2))
                ) as r (arr, elements)
        group by 1
        having min(arr) = max(arr)
        )
)
$$ language sql strict immutable;

Upvotes: 0

Daniel Vérité
Daniel Vérité

Reputation: 61506

The && operator is a predicate that yields a true or false result, not a list of values.

If you're looking for the list of order_id that exist for both client_id=1 and client_id=2, the query would be:

 select order_id from simple_table  where client_id in (1,2)
  group by order_id having count(*)=2;

That's equivalent to the intersections of the two arrays if you consider that these arrays are sets (no duplicates and the positions of the values are irrelevant), except that you don't need to use arrays at all, simple standard SQL is good enough.

Upvotes: 2

Related Questions