Re: Double Denormalizing in Postgres - Mailing list pgsql-general

From Pavel Stehule
Subject Re: Double Denormalizing in Postgres
Date
Msg-id CAFj8pRCgutRpmXeyUF4EUjrNZiVAa-Pa6OGXTPyKeqzugU_O3g@mail.gmail.com
Whole thread Raw
In response to Double Denormalizing in Postgres  (Robert James <srobertjames@gmail.com>)
Responses Re: Double Denormalizing in Postgres
List pgsql-general
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

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: Philosophical question
Next
From: Carlos Mennens
Date:
Subject: Streaming Replication Configuration