pg_dump -> import traject using “upsert” paradigm?

Question

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.

Answer 1

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