Comparing two rows in PostgreSQL

Question

I have an employee table in two different servers with the same structure. I want to shift all the data to main server but there are some records already exists in main server that are duplicate of local one's but are incomplete, now i want to compare these records so i can chose the most completed one and delete the incomplete one from one of the servers. surely i will use dblink to compare but what would be the SQL query for it?

in short some of the records are incomplete in local server while some of them are incomplete in main. is there any way to compare them and chose the completest one?

Thank you in advance.

Answer 1

instead of dblink, I recommend you to use potgres_fdw.

When you map the foreign table, you can use a simple SELECT INSERT to solve your problem. E.g.:

INSERT INTO remote.employee
SELECT local.* FROM public.employee AS local
WHERE NOT EXISTS ( 
     SELECT 1 FROM remote.employe AS remote
     WHERE local.id = remote.id
)

Remember to fix the WHERE filter to fit your table's primary keys.

EDIT: I make a small sample how to compare the rows of both tables as a gist: https://gist.github.com/sebastianwebber/7b4ab352733e8aab8b03f94d8aa0c7a9

The easy way to compare the rows is:

SELECT
    e1.id as e1_pk,
    e2.id as e2_pk,
    COALESCE(e1.* = e2.*, false) as equal
FROM employee1 as e1
RIGHT JOIN employee2 as e2 USING (id);

Hope it helps.

Answer 2

Typically, if you have the key column with the same values for the corresponding records you can use full outer join, and use the key column (i.e. ID) as the join key. the query would be like

select a.id, a.name, a.surname.., b.id, b.name, b.surname.. 
from local_customer a full outer join remote_customer b on a.id = b.id

If there is a chance that the same ID points to different customers in those databases, you have to use other columns, like name, address, zip code etc - potentially creating a composite join key.

select a.id, a.name, a.surname.., b.id, b.name, b.surname.. 
from local_customer a full outer join remote_customer b
on a.name = b.name and a.surname = b.surname

I wrote a data comparison utility, and a free version is available for download. Have a look if it might be of use to you. https://selectcompare.com