Re: Double Denormalizing in Postgres

On Dec 15, 2011, at 11:27, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> 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,
> ...
>

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.