X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A70E7D1BCB2 for ; Wed, 10 Mar 2004 22:46:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 11118-08 for ; Wed, 10 Mar 2004 18:46:06 -0400 (AST) Received: from krogh.officenet.no (cD9089A13.sdsl.catch.no [217.8.154.19]) by svr1.postgresql.org (Postfix) with ESMTP id 501AFD1B461 for ; Wed, 10 Mar 2004 18:46:03 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by krogh.officenet.no (Postfix) with ESMTP id 234F313866C; Wed, 10 Mar 2004 17:33:39 -0500 (EST) From: Andreas Joseph Krogh Organization: OfficeNet AS To: Marty Scholes Subject: Re: Inserting data in a table using sub-selects] Date: Wed, 10 Mar 2004 23:33:38 +0100 User-Agent: KMail/1.6 Cc: pgsql-sql References: <404F810E.1010707@outputservices.com> In-Reply-To: <404F810E.1010707@outputservices.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200403102333.38174.andreak@officenet.no> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/122 X-Sequence-Number: 17221 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wednesday 10 March 2004 21:56, Marty Scholes wrote: > INSERT INTO table2 (id, content) ( > SELECT id, coalesce(title, '') || ' ' || coalesce(description, '') > FROM table1 t1); > > If you want to keep them syncrhonized, in other words, rerun the query > over and over again without having to truncate table2 first or deleting > all of the rows, you can: > > INSERT INTO table2 (id, content) ( > SELECT id, coalesce(title, '') || ' ' || coalesce(description, '') > FROM table1 t1) > WHERE id NOT IN ( > SELECT id > FROM table1); > > With Oracle there is a slick way to do a partial outer join that allowed > you to do this without creating a complete list of table1.id in the last > subquery, but I dunno if Pg has an equivalent mechanism. Thanks, works perfectly. - --=20 Andreas Joseph Krogh Managing Director, Senior Software Developer OfficeNet AS I always do a CVS update before making a patch (unless I forget). gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQFAT5fCUopImDh2gfQRArmFAJ4qx31Wx31sehCBo7qfjPwwPWvUzACguw9t H8mWrl+TsdYIhmQ+FDu41t8=3D =3Dqj1H -----END PGP SIGNATURE-----