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

Re: Inserting data in a table using sub-selects]

From: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
To: Marty Scholes <marty(at)outputservices(dot)com>
Cc: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Inserting data in a table using sub-selects]
Date: 2004-03-10 22:33:38
Message-ID: 200403102333.38174.andreak@officenet.no (view raw or flat)
Thread:
Lists: pgsql-sql
-----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(at)officenet(dot)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-----

In response to

pgsql-sql by date

Next:From: Richard GrosseDate: 2004-03-10 22:51:03
Subject: Re: Converting query to view - duplicate fields - solved
Previous:From: Jonathan GardnerDate: 2004-03-10 22:09:08
Subject: Re: Break a report in Run Time

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