0

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.

3
  • What do you mean by completest? Fewer nulls?
    – Lennart
    Aug 27, 2016 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. Aug 27, 2016 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. Aug 27, 2016 at 13:30

2 Answers 2

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.

4
  • 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. Aug 27, 2016 at 13:49
  • @Sayad do you need to update the remote rows too? or just compare? Aug 27, 2016 at 13:53
  • initially i just want to compare them Aug 27, 2016 at 14:24
  • @Sayad I just update and make a sample, take a look. Aug 27, 2016 at 14:45
0

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

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.