Re: update and group by/aggregate

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: update and group by/aggregate
Date: 2008-08-27 10:44:20
Message-ID: 20080827104419.GE7271@frubble.xen.chris-lamb.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, Aug 27, 2008 at 10:32:43AM +0200, Ivan Sergio Borgonovo wrote:
> How can I update catalog_items.Authors
>
> create table catalog_items(
> ItemID bigint primary key,
> Authors varchar(1024)
> );

The type "varchar(1024)" looks a little awkward, wouldn't an unadorned
TEXT be easier? if you explicitly want to limit things to 1024
characters then what you're doing is correct.

> taking results from
>
> select ia.ItemID, array_accum(trim(' \t]' from a.Name))
> from catalog_itemauthor ia
> join catalog_author a on a.AuthorID=ia.AuthorID
> where a.Name is not null and length(trim(' \t' from a.Name))>1
> group by ia.ItemID;

UPDATE catalog_items SET authors=array_to_string(x.authors,', ')
FROM (
SELECT ia.itemid, array_accum(trim(' \t]' from a.name)) AS authors
FROM catalog_itemauthor ia
JOIN catalog_author a ON a.authorid = ia.authorid
WHERE a.name IS NOT NULL
AND length(trim(' \t' from a.name))>1
GROUP BY ia.itemid) x;

is a reasonably direct translation. Though I may be tempted to use
something more like:

UPDATE catalog_items SET authors=array_to_string(x.authors,', ')
FROM (
SELECT ia.itemid, array_accum(a.name) AS authors
FROM catalog_itemauthor ia, (
SELECT authorid, trim(' \t' from name) AS name
FROM catalog_author) a
WHERE ia.authorid = a.authorid
AND a.name IS NOT NULL
AND length(a.name) > 1
GROUP BY ia.itemid) x;

to ensure that the characters trimmed from the authors' names are
consistent.

Sam

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sam Mason 2008-08-27 10:51:42 Re: update and group by/aggregate
Previous Message Terry Lee Tucker 2008-08-27 09:36:44 Re: ENABLE / DISABLE ALL TRIGGERS IN DATABASE