Generating unique combinations in postgres of size two from an array

Question

I have a followup to this question in which I generated arrays of this type of table:

 val | fkey | num
 ------------------
 1   |  1   | 10
 1   |  2   | 9  
 1   |  3   | 8  
 2   |  3   | 1

In which the resulting returned row would like something like this (fkeys were essentially aggregated to one list):

1 | [1,2,3]

What I would like to do is modify the query with respective to the value in the 'num' column. That is, I would like something like:

1 | [1,2] | [10, 9]
1 | [1,3] | [10, 8]
1 | [2,3] | [9, 8]

The ordering of the third column in the return query doesn't bother me. Right now I have something like this:

SELECT val, array_agg(fkey), array_agg(num)
FROM mytable
GROUP BY val
Having Count(fkey) > 1

But that returns something more like:

1 | [1,2,3] | [10, 9, 8]

Which would be ok, except I can't easily tell which number in the third array comes from what fkey (if that makese sense). Something like this would work to keep track of it:

1 | [1,2,3] | [10 - 1, 9 - 2, 8 - 3]

I'm not sure what the best way to go about doing this is, but I'm open to suggestions.

EDIT: I'm on Postgres 9.3.6. The table definition is:

awesome-db=# \d mytable
Table "public.mytable"
Column    |  Type   | Modifiers 
----------+---------+-----------
 val      | bytea   | not null
 fkey     | uuid    | not null
 num      | integer | not null
 Indexes:
"comp_key" UNIQUE CONSTRAINT, btree (fkey, num, val)
"fingerprint_index" btree (val)

Two `array_agg()` calls in the same `SELECT` list take elements in the same order unless instructed otherwise with `ORDER BY` added to the function parameters. So you *can* tell which elements of the two arrays go together and your current function should do the job. What am I missing? — Erwin Brandstetter, Apr 01 '15 at 08:08
Generally, a question like this requires that you provide the table definition (`\d mytable` in psql) and your version of Postgres. — Erwin Brandstetter, Apr 01 '15 at 08:23
It is *much* better to copy / paste what you get from `\d mytable` in psql. Else we cannot be sure about the complete picture. For instance, I still don't know whether your columns are defined `NOT NULL`. — Erwin Brandstetter, Apr 02 '15 at 02:46

score 1 · Accepted Answer · answered Apr 01 '15 at 06:16

you need a self join using row_number:

select t1.val,t1.fkey||','||t2.fkey,t1.num||','|| t2.num
from (select row_number() over(order by val) rn,
       val,
       fkey,
       num
from mytable) t1 
join (select row_number() over(order by val) rn,
       val,
       fkey,
       num
from mytable) t2
on t1.val=t2.val and t1.rn<t2.rn

SQLFIDDLE DEMO

Erwin Brandstetter · Answer 2 · 2015-04-02T04:17:08.520

What you have does what you are asking for: the order of elements corresponds in both arrays.

Your first idea:

With a UNIQUE constraint on the combination (val, fkey, num) and NOT NULL constraints on all columns, you get arrays of two elements (smaller (num, fkey) first) like this:

SELECT t1.val
     , ARRAY[t1.num, t2.num] AS nums
     , ARRAY[t1.fkey, t2.fkey] AS fkeys
FROM   mytable t1 
JOIN   mytable t2 USING (val)
WHERE  (t1.num, t1.fkey) < (t2.num, t2.fkey);

Or your second idea:

SELECT val, array_agg(num) AS nums, array_agg(num::text || ' - ' || fkey) AS fkeys
FROM   (
   SELECT *
   FROM   mytable
   ORDER  BY num, fkey
   ) sub
GROUP  BY val
HAVING count(*) > 1;

SQL Fiddle.

I added some information - the unique constraint is between all three columns. It's possible to have a duplication of (val, fkey) in my definition. — Clicquot the Dog, Apr 01 '15 at 16:40

Generating unique combinations in postgres of size two from an array

2 Answers2