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

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.



Andreas Joseph Krogh wrote:> -----BEGIN PGP SIGNED MESSAGE-----> Hash: SHA1>> Hi, I'd like to fill one table with the
contentsof another table. 
 
Mye schema> is like this:>> CREATE TABLE table1(> id serial NOT NULL PRIMARY KEY,> title varchar NOT NULL,> description
varchar>);>> CREATE TABLE table2(> id int NOT NULL REFERENCES(table1(id) ON DELETE CASCADE,> content varchar NOT NULL>
);>>Now - is there a way I can do something like:> INSERT INTO table2(id, content) values (select t1.id, 
 
coalesce(t1.title, '')> || ' ' || coalesce(t1.description, '') as content from table1 t1);>> Any hints on how to insert
alot of values at the same time like this?>> - --> Andreas Joseph Krogh <andreak@officenet.no>> Managing Director,
SeniorSoftware 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)>>iD8DBQFAT3H1UopImDh2gfQRAsa6AJ9jZjNz25w4iVnxNJYY9LJuG0HBLACfZfup> 1TMzQSi1+YYgNjpcampX6wo=> =fJA3>
-----ENDPGP SIGNATURE----->> ---------------------------(end of broadcast)---------------------------> TIP 7: don't
forgetto increase your free space map settings