Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group