up vote 4 down vote favorite

If I use array_agg to collect names, I get my names separated by commas, but in case there is a null value, that null is also taken as a name in the aggregate. For example :

SELECT g.id,
       array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END) canonical_users,
       array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END) non_canonical_users
FROM groups g
GROUP BY g.id;

it returns ,Larry,Phil instead of just Larry,Phil (in my 9.1.2, it shows NULL,Larry,Phil). as in this fiddle

Instead, if I use string_agg(), it shows me only the names (without empty commas or nulls) like here

The problem is that I have Postgres 8.4 installed on the server, and string_agg() doesn't work there. Is there any way to make array_agg work similar to string_agg() ?

share|improve this question
See this PostgreSQL mailing list thread on much this topic: postgresql.1045698.n5.nabble.com/… – Craig Ringer Oct 29 '12 at 14:16
I am sorry, I don't think there is a solution in that thread.. – Daud Oct 29 '12 at 14:33
There are two solutions in that thread. One is to create a function and the other (just suggested not shown) is the one I answered. – Clodoaldo Oct 29 '12 at 14:37
@Clodoaldo - all the rows will have canonical in ('y','n')... so the where clause seems to be redundant. The problem is that inside a grouping, if the the value of the canonical field is 'Y', and we are collecting 'N's, then a null be collected too.. – Daud Oct 29 '12 at 14:44
Ok. Now I got it. Check the update answer. – Clodoaldo Oct 29 '12 at 14:51
show 3 more comments
feedback

3 Answers

up vote 4 down vote accepted

SQL Fiddle

select
    id,
    (select array_agg(a) from unnest(canonical_users) a where a is not null) canonical_users,
    (select array_agg(a) from unnest(non_canonical_users) a where a is not null) non_canonical_users
from (
    SELECT g.id,
           array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END) canonical_users,
           array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END) non_canonical_users
    FROM groups g
    GROUP BY g.id
) s

Or, simpler and may be cheaper, using array_to_string which eliminates nulls:

SELECT
    g.id,
    array_to_string(
        array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE NULL END)
        , ','
    ) canonical_users,
    array_to_string(
        array_agg(CASE WHEN g.canonical = 'N' THEN g.users ELSE NULL END)
        , ','
    ) non_canonical_users
FROM groups g
GROUP BY g.id

SQL Fiddle

share|improve this answer
This won't work. Please try it in the fiddle I provided.. – Daud Oct 29 '12 at 14:32
Thanks. But if the main query (s) returns a 1000 rows, then the 2 subqueries (using unnest) will run once for each row.. Will it be better to tolerate NULLs than executing 2000 extra select queries ? – Daud Oct 29 '12 at 17:07
@Daud New version which could be cheaper. Take the explain output of both to be sure. – Clodoaldo Oct 29 '12 at 17:34
@Clodoaldo If you're using array_to_string(array_agg(...)) you might as well use string_agg. – Craig Ringer Oct 29 '12 at 23:44
@Craig The problem in the question is 8.4 – Clodoaldo Oct 29 '12 at 23:57
show 1 more comment
feedback
up vote 0 down vote

A bigger question is why pull all user/group combos at once. Guaranteed your UI cant handle all that data. Adding paging to oversized data is also a bad idea. Get your users to filter the set before they see data. Make sure your JOIN option set is in the list so they can filter for performance if they want to. Sometimes 2 queries make users happier if they are both fast.

share|improve this answer
feedback
up vote 3 down vote

As has been suggested in the comments you can write a function to replace nulls in an array, however as also pointed out in the thread linked to in the comments, this kind of defeats the efficiency of the aggregate function if you have to create an aggregate, split it then aggregate it again.

I think keeping nulls in the array is just a (perhaps unwanted) feature of Array_Agg. You could use subqueries to avoid this:

SELECT  COALESCE(y.ID, n.ID) ID,
        y.Users,
        n.Users
FROM    (   SELECT  g.ID, ARRAY_AGG(g.Users) AS Users
            FROM    Groups g
            WHERE   g.Canonical = 'Y'
            GROUP BY g.ID
        ) y
        FULL JOIN 
        (   SELECT  g.ID, ARRAY_AGG(g.Users) AS Users
            FROM    Groups g
            WHERE   g.Canonical = 'N'
            GROUP BY g.ID
        ) n
            ON n.ID = y.ID

SQL FIDDLE

share|improve this answer
Thanks. But I needed 'case' to handle rows within a given grouping, and subqueries would be inefficient there – Daud Oct 29 '12 at 15:15
feedback

Your Answer

 
or
required, but never shown
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.