How can I compare two same PostgreSQL tables for equality of the data at row level?-CodePudding

I want to compare two same PostgreSQL tables to check if they are equals in rows data, so if they are not equals I want to see the different rows. How can I do that automatically? Is there any library or a tool for that?

(I need to check that with Java language but if you know other ways please share)

CodePudding user response：

What about pure SQL? You can translate it into JDBC then

From this website

SELECT
    ID,
    NAME,
    'not in bar' AS note
FROM
    foo
EXCEPT
    SELECT
        ID,
        NAME,
        'not in bar' AS note
    FROM
        bar

OR this

SELECT
    id,
    name
FROM
    foo
FULL OUTER JOIN bar USING (id, name)
WHERE
    foo.id IS NULL
OR bar.id IS NULL;

CodePudding user response：

Based on your comment, you have the same primary key but may have different data. Lets consider two tables customer and customer_backup with the following schema:

create table customer (id integer, firstname varchar(100), lastname varchar(100));
    insert into customer (id, firstname, lastname) values (1, 'John', 'Doe');
    insert into customer (id, firstname, lastname) values (2, 'Test', 'Test');
    insert into customer (id, firstname, lastname) values (3, 'Jack', 'Smith');
    
create table customer_backup (id integer, firstname varchar(100), lastname varchar(100));
    insert into customer_backup (id, firstname, lastname) values (1, 'John', 'Doe');
    insert into customer_backup (id, firstname, lastname) values (2, 'Test', 'Jack');
    insert into customer_backup (id, firstname, lastname) values (3, 'Will', 'Smith');

So to find the diffrences you could specify in the where clause all the fields from the first table, that must not be equal to all the fields from the second table:

select *
from customer c
         join customer_backup b on c.id = b.id
where c.firstname <> b.firstname
   or c.lastname <> b.lastname
 # or c.otherField <> (not equal) b.otherField

You can check this working example.