Re: Double Denormalizing in Postgres

2011/12/15 Robert James <srobertjames(at)gmail(dot)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

>
> How can I achieve this? (I'm using Postgres 8.3)
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general