Обсуждение: Double Denormalizing in Postgres

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

How can I achieve this? (I'm using Postgres 8.3)

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


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

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
resultantarray.  That where clause is why you cannot simply do: 

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

David J.