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.

  • What do you mean by completest? Fewer nulls? – Lennart Aug 27 '16 at 13:00
  • 1
    Use a foreign data wrapper, not a dblink. A dblink won't let you join the two table, with a foreign table you can do that and compare the contents. – a_horse_with_no_name Aug 27 '16 at 13:22
  • by completest i mean, some of the values were transfered but not all of them for example, name, last name , id were transfered but grade, degree were not. – Sayad Xiarkakh Aug 27 '16 at 13:30

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
     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:

    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.

|improve this answer|||||
  • Thank you for the answer, but i need to compare two tables for a specific range of IDs, let me say it more clearly, suppose i have employee with ID 2 in both servers, now one in the local server has complete date and in the main it is incomplete means degree, grade, school_name.. are not transfered to main server or vise versa, i want to compare them and find the most complete one and delete the other so i can run the sync again. I wish it is clear this time. – Sayad Xiarkakh Aug 27 '16 at 13:49
  • @Sayad do you need to update the remote rows too? or just compare? – Sebastian Webber Aug 27 '16 at 13:53
  • initially i just want to compare them – Sayad Xiarkakh Aug 27 '16 at 14:24
  • @Sayad I just update and make a sample, take a look. – Sebastian Webber Aug 27 '16 at 14:45

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

|improve this answer|||||

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.