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