Postgresql: only keep unique values from integer array

Question

Let's say I have an array of integers

1  6 6  3 3  8  4 4

It will be always of the form n*(pairs of number) + 2 (unique numbers).

Is there an efficient way of keeping only the 2 uniques values (i.e. the 2 with single occurence)?

Here, I would like to get 1 and 8.

So far is what I have:

SELECT node_id 
FROM 
( SELECT node_id, COUNT(*) 
  FROM unnest(array[1,  6, 6 , 3, 3 , 8 , 4 ,4]) AS node_id 
  GROUP BY node_id 
) foo 
ORDER BY count LIMIT 2;

Gordon Linoff · Accepted Answer · 2015-08-07 12:27:12Z

3

You are very close, I think:

SELECT node_id 
FROM (SELECT node_id, COUNT(*) 
      FROM unnest(array[1,  6, 6 , 3, 3 , 8 , 4 ,4]) AS node_id 
      GROUP BY node_id 
      HAVING count(*) = 1
     ) foo ;

You can group these back into an array, if you like, using array_agg().

answered Aug 7, 2015 at 12:27

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql: only keep unique values from integer array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related