Re: update and group by/aggregate

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