Double Denormalizing in Postgres

From: Robert James <srobertjames(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Double Denormalizing in Postgres
Date: 2011-12-15 16:22:18
Message-ID: CAGYyBggDaLCkMM3wcWvmy_1wwaOJrP=UwOTiWhMMehVhVvGdvQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2011-12-15 16:27:50 Re: Double Denormalizing in Postgres
Previous Message Adrian Klaver 2011-12-15 16:18:23 Re: Server/Data Migration Advice