sync data between two postgres database tables

Question

I have a db1 table A, and db2 table B i want to insert only non-existing rows from table A to table B and if data already exist in table B, update it. what is the best way to perform this? i have hundreds of rows to insert and update and many tables. i’m using dbvisualizer. Thanks.

Answer 1

One method uses a not exists subquery. Something like this:

insert into b (col1, . . . )
    select col1, . . . 
    from a
    where not exists (select 1 from b where b.? = a.?);

There are other methods. If you have a unique constraint/index on b that defines uniqueness, then you might want an on conflict clause instead. If you are trying to prevent duplicates, then a unique constraint/index is the correct solution.