Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

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)
share|improve this question
    
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 1 '15 at 8:08
1  
Generally, a question like this requires that you provide the table definition (\d mytable in psql) and your version of Postgres. – Erwin Brandstetter Apr 1 '15 at 8:23
1  
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 2 '15 at 2:46
up vote 1 down vote accepted

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

share|improve this answer

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.

share|improve this answer
    
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 1 '15 at 16:40

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.