0

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
6
  • are the databases on thbe same server? even then make a backup from database A and import into B – nbk Jan 29 at 14:32
  • no? They are aon different servers. "I have a table running on a database at localhost (donor database A), and a receiving table running at another server externally " – paul23 Jan 29 at 14:33
  • @nbk what do you mean? Can you explain "zjrm try z" – paul23 Jan 29 at 14:43
  • make a backup dump on server A and import it as file in Server B(localhost) – nbk Jan 29 at 14:44
  • @nbk but how do I prevent it from overwriting the table data? - And if I go through a temporary table, how do I make sure the ids are correctly set up? – paul23 Jan 29 at 15:09
0

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)
;

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.