current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 1 down vote favorite

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!

share|improve this question
    
Thanks for including sample data and what you're trying to achieve, +1. It'd be good if you could show exactly what problems you are having (unexpected results, error message, etc) and the exact text of the SQL you ran, though. Finally, please always include your PostgreSQL version (SELECT version()) in questions. – Craig Ringer Jul 3 '13 at 1:34
    
Sorry. 9.2 on OSX 10.6.8 – Jim Miller Jul 3 '13 at 13:24

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
PERFECT!! Exactly what I was looking for! Thanks Daniel! So if I had multiple client_ids: where client_id in (1,2) would I write: where client_id in (1,2,3,4)? – Jim Miller Jul 3 '13 at 13:31
    
Yes, and count(*)=4 – Daniel Vérité Jul 3 '13 at 13:44
    
Great! Thanks again! – Jim Miller Jul 3 '13 at 14:31
up vote 0 down vote

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;
share|improve this answer
    
Thanks BMA! Very helpful. I appreciate your help!! – Jim Miller Jul 3 '13 at 13:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.