"merge" two tables -from different databases- together?

Question

Well say I have a table running on a database at localhost (donor database A), and a receiving table running at another server externally (database B) With table creation (on both sides) like:

CREATE TABLE public.my_table (
    id integer NOT NULL,
    key text NOT NULL UNIQUE,
    description text,
    PRIMARY KEY (id)
);


CREATE SEQUENCE public.my_table_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.my_table ALTER COLUMN id SET DEFAULT nextval('public.language_dictionary_id_seq'::regclass)

Now I wish to merge from database A into database B. However only merging should happen on keys that are not existing on database B, and id should be recalculated (obviously to not have duplicates).

Basically as if each row from database A is insert into database B using:

INSERT INTO public.my_table (key, description)
VALUES (...)
ON CONFLICT DO NOTHING

Answer 1

In MS SQL there is a MERGE statement that meets your requirements. PostgreSQL has the same. Read more: https://www.postgresql.org/message-id/attachment/23520/sql-merge.html

It allows full control about conditions when to add a row, update a row or delete a row in the target db. I have frequently used it. But it is not a high performance operation if used with a lot of rows.

Of course you need to take care for a direct connection between the servers running the databases.

Sample:

MERGE CustomerAccount CA
USING (SELECT CustomerId, TransactionValue, 
   FROM Transactions
   WHERE TransactionId > 35345678) AS T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED 
  UPDATE SET Balance = Balance - TransactionValue
WHEN NOT MATCHED
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionValue)
;