Double Denormalizing in Postgres

2011/12/15 Robert James <srobertjames@gmail.com>:

To match the heavily denormalized format of a legacy app, I need to
take a query which gives this:

name | product | rent | own
Bob | Car | true | false
Bob | Car | false | true
Bob | Bike | false | true
Bob | Truck | true | true

and denormalize it into this:

name | rented_products | owned_products
Bob | {Car, Truck} | {Car, Truck, Bike}

I thought I could do this using array_agg, but I don't see how to do
that on a condition.  In pseudocode, I'd like to do this:
SELECT
uniq(array_agg(product WHERE rent)) AS rented_products,
uniq(array_agg(product WHERE own)) AS owned_products
...

CREATE OR REPLACE array_uniq(anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT DISTINCT unnest($1))
$$ LANGUAGE sql;

SELECT array_uniq(array_agg(CASE WHEN rent THEN product ELSE NULL
END)) AS rented_product,
...

Regards

Pavel Stehule

On Dec 15, 2011, at 11:27, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2011/12/15 Robert James <srobertjames@gmail.com>:

To match the heavily denormalized format of a legacy app, I need to
take a query which gives this:

name | product | rent | own
Bob | Car | true | false
Bob | Car | false | true
Bob | Bike | false | true
Bob | Truck | true | true

and denormalize it into this:

name | rented_products | owned_products
Bob | {Car, Truck} | {Car, Truck, Bike}

I thought I could do this using array_agg, but I don't see how to do
that on a condition. In pseudocode, I'd like to do this:
SELECT
uniq(array_agg(product WHERE rent)) AS rented_products,
uniq(array_agg(product WHERE own)) AS owned_products
...

CREATE OR REPLACE array_uniq(anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT DISTINCT unnest($1))
$$ LANGUAGE sql;

SELECT array_uniq(array_agg(CASE WHEN rent THEN product ELSE NULL
END)) AS rented_product,
...

You need a WHERE "unnested column" IS NOT NULL within the function to remove the artificially introduced NULLs from the resultant array. That where clause is why you cannot simply do:

ARRAY_AGG(DISTINCT CASE WHEN ... THEN ... ELSE ... END)

David J.