Pedro Doria Meunier wrote:
Hi all, This is perhaps a very 'newbie' question but for the life of me, I can't see a way to do it without resorting to programming... (blush)
As far as I'm concerned writing SQL is programming. It's not (usually) procedural, but neither is Prolog, ML, Haskell, or any other number of programming languages.
Anyway ... your question doesn't seem to be all that specific, but it sounds like you've done a database migration where the data from one table was excluded from the migration. You now wish to copy that table's contents to the new server as well, merging it with the data in the same table that's been created since the migration.
If it's just a history table I don't really see what's wrong with doing a data-only dump of just that table using pg_dump, removing any DELETE or TRUNCATE statements from the dump script, then running the load script on the new server. That does assume you reserved space in any primary key ID sequence in the new table though.
If there are primary key ID conflicts and you don't actually care about the primary key values you may be able to load the data into a temporary table then do an INSERT...SELECT that generates new primary keys with `nextval' / DEFAULT and copies all other fields.
Personally I don't bother putting a primary key on history tables unless it needs to be accessed by an ORM layer or similar, but I realise lots of people do.
If all the above completely misses the point then either I've totally misread your question or you might want to explain it in a bit more detail.
-- Craig Ringer
