¿cómo excluir valores nulos en array agg como en string agg usando postgres?

Con postgresql-9.3 uno puede hacer esto;

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

Actualización : con postgresql-9.4;

SELECT g.id,
   array_agg(g.users) FILTER (WHERE g.canonical = 'Y') canonical_users,
   array_agg(g.users) FILTER (WHERE g.canonical = 'N') non_canonical_users
FROM groups g 
GROUP BY g.id;

Para resolver la cuestión general de eliminar los nulos de los agregados de matrices, hay dos formas principales de atacar el problema: o bien haciendo array_agg(unnest(array_agg(x)) o creando un agregado personalizado.

El primero es de la forma mostrada arriba :

SELECT 
    array_agg(u) 
FROM (
    SELECT 
        unnest(
            array_agg(v)
        ) as u 
    FROM 
        x
    ) un
WHERE 
    u IS NOT NULL;

El segundo:

/*
With reference to
http://ejrh.wordpress.com/2011/09/27/denormalisation-aggregate-function-for-postgresql/
*/
CREATE OR REPLACE FUNCTION fn_array_agg_notnull (
    a anyarray
    , b anyelement
) RETURNS ANYARRAY
AS $$
BEGIN

    IF b IS NOT NULL THEN
        a := array_append(a, b);
    END IF;

    RETURN a;

END;
$$ IMMUTABLE LANGUAGE 'plpgsql';

CREATE AGGREGATE array_agg_notnull(ANYELEMENT) (
    SFUNC = fn_array_agg_notnull,
    STYPE = ANYARRAY,
    INITCOND = '{}'
);

Llamar al segundo es (naturalmente) un poco más bonito que el primero:

Seleccione array_agg_notnull (v) desde x;

Estoy agregando esto a pesar de que este hilo es bastante viejo, pero me encontré con este truco limpio que funciona bastante bien en arreglos pequeños. Se ejecuta en Postgres 8.4 + sin bibliotecas o funciones adicionales.

string_to_array(array_to_string(array_agg(my_column)))::int[]

El método array_to_string() en realidad se deshace de los nulos.

Como se ha sugerido en los comentarios, puede escribir una función para reemplazar los nulos en una matriz, sin embargo, como también se señaló en el hilo vinculado en los comentarios, este tipo de derrota la eficiencia de la función aggregate si tiene que crear un agregado, dividirlo y agregarlo de nuevo.

Creo que mantener null en el array es solo una característica (quizás no deseada) de Array_Agg. Puedes usar subconsultas para evitar esto:

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

VIOLÍN SQL

Una pregunta más grande es por qué tirar de todos los combos de usuario/grupo a la vez. Garantizado su interfaz de usuario no puede manejar todos esos datos. Agregar paginación a datos sobredimensionados también es una mala idea. Haz que los usuarios filtren el conjunto antes de que vean los datos. Asegúrese de que su conjunto de opciones de unión esté en la lista para que puedan filtrar el rendimiento si lo desean. A veces 2 consultas hacen a los usuarios más felices si ambos son rápidos.