Re: Copying data from a table to another database

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