From: Andreas Joseph Krogh <andreak@officenet.no>
To: Marty Scholes <marty@outputservices.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Inserting data in a table using sub-selects]
Date: Wed, 10 Mar 2004 23:33:38 +0100
Message-ID: <200403102333.38174.andreak@officenet.no> (raw)
In-Reply-To: <404F810E.1010707@outputservices.com>
References: <404F810E.1010707@outputservices.com>
-----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.
- --
Andreas Joseph Krogh <andreak@officenet.no>
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=
=qj1H
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreak@officenet.no, marty@outputservices.com
Subject: Re: Inserting data in a table using sub-selects]
In-Reply-To: <200403102333.38174.andreak@officenet.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox