X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8837E9DCC73 for ; Mon, 19 Dec 2005 13:12:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87862-01 for ; Mon, 19 Dec 2005 13:12:35 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ll.mit.edu (LLMAIL.LL.MIT.EDU [129.55.12.40]) by postgresql.org (Postfix) with ESMTP id 62B649DCC06 for ; Mon, 19 Dec 2005 13:12:33 -0400 (AST) Received: (from smtp@localhost) by ll.mit.edu (8.12.10/8.8.8) id jBJHCXj2016829 for ; Mon, 19 Dec 2005 12:12:33 -0500 (EST) Received: from UNKNOWN( ), claiming to be "sty.llan" via SMTP by llmail, id smtpdAAAFbaaGF; Mon Dec 19 12:12:11 2005 Date: Mon, 19 Dec 2005 12:12:11 -0500 From: george young To: pgsql-sql@postgresql.org Subject: Re: how to convert relational column to array? Message-Id: <20051219121211.002f7e87.gry@ll.mit.edu> In-Reply-To: <20051219165449.GA99764@winnie.fuhr.org> References: <20051219110612.26de2268.gry@ll.mit.edu> <20051219165449.GA99764@winnie.fuhr.org> Reply-To: gry@ll.mit.edu Organization: MIT Lincoln Laboratory X-Mailer: Sylpheed version 2.0.1 (GTK+ 2.8.0; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200512/169 X-Sequence-Number: 23711 On Mon, 19 Dec 2005 09:54:49 -0700 Michael Fuhr threw this fish to the penguins: > On Mon, Dec 19, 2005 at 11:06:12AM -0500, george young wrote: > > create table new_tab(name text, id int, permits text[]); > > > > -- I insert one row per name: > > insert into new_tab select distinct name,id,cast('{}' as text[]) from old_tab; > > > > Now I want to fold all the 'permits' values into the new permits arrays. > > In PostgreSQL 7.4 and later you can build an array from a select, > so I think the following update should work (it did for me when I > tested it): > > UPDATE new_tab SET permits = array( > SELECT permits > FROM old_tab > WHERE old_tab.name = new_tab.name AND old_tab.id = new_tab.id > ); That's exactly what I needed. Works great. Thanks, George -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL)