0

Well say I have two physically separate database and I wish to replace database B with data from A. Except that I wish to keep the data in B which does not exist in A?

So basically I'd go over each entry in A and use an insert-or-replace like:

INSERT INTO table_name (name, value)
VALUES ('hello', 'world')
ON CONFLICT (name) do nothing;

IE the following databases would be merged into the third:

A)
name | value
A    | 1
B    | 2

B)
name | value
B    | 10
C    | 20

MERGED)
name | value
A    | 1
B    | 2
C    | 20

If I use the -c option from pg_dump it would just throw away the "c" value in the merged table.

0

I'd simply restore the table in another schema and forge my upsert command to insert/update B data from A data.

If you're using the plain text format using pg_dump, you'll be able to change the search_path at the beginning of the file to create your new table on the new schema.

If you're using a custom format, you can either get the SQL format using pg_restore and change it or use the -l flag to get the list file and edit it accordingly.

Have a nice day

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.