sql - Generating unique combinations in postgres of size two from an array -

i have followup this question in generated arrays of type of table:

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

in resulting returned row (fkeys aggregated 1 list):

1 | [1,2,3] 

what modify query respective value in 'num' column. is, like:

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

the ordering of third column in return query doesn't bother me. right have this:

select val, array_agg(fkey), array_agg(num) mytable group val having count(fkey) > 1 

but returns more like:

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

which ok, except can't tell number in third array comes fkey (if makese sense). work keep track of it:

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

i'm not sure best way go doing is, i'm open suggestions.

edit: i'm on postgres 9.3.6. 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) 

you need self join using row_number:

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

sqlfiddle demo