Checking whether two tables have identical content in PostgreSQL

Question

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table.

A PostgreSQL solution would be fine, but a generic one would be better. I found http://www.besttechtools.com/articles/article/sql-query-to-check-two-tables-have-identical-data, but I don't understand the logic used.

Background: I re-wrote some database generating code, so I need to check whether the old and new code produce identical results.

Answer 1

One option is to use a FULL OUTER JOIN between the two tables in the following form:

SELECT count (1)
    FROM table_a a
    FULL OUTER JOIN table_b b 
        USING (<list of columns to compare>)
    WHERE a.id IS NULL
        OR b.id IS NULL ;

For example:

CREATE TABLE a (id int, val text);
INSERT INTO a VALUES (1, 'foo'), (2, 'bar');

CREATE TABLE b (id int, val text);
INSERT INTO b VALUES (1, 'foo'), (3, 'bar');

SELECT count (1)
    FROM a
    FULL OUTER JOIN b 
        USING (id, val)
    WHERE a.id IS NULL
        OR b.id IS NULL ;

Will return a count of 2, whereas:

CREATE TABLE a (id int, val text);
INSERT INTO a VALUES (1, 'foo'), (2, 'bar');

CREATE TABLE b (id int, val text);
INSERT INTO b VALUES (1, 'foo'), (2, 'bar');

SELECT count (1)
    FROM a
    FULL OUTER JOIN b 
        USING (id, val)
    WHERE a.id IS NULL
        OR b.id IS NULL ;

returns the hoped for count of 0.

The thing I like about this method is that it only needs to read each table once vs. reading each table twice when using EXISTS. Additionally, this should work for any database that supports full outer joins (not just Postgresql).

I generally discourage use of the USING clause but here is one situation where I believe it to be the better approach.

Addendum 2019-05-03:

If there is an issue with possible null data, (i.e. the id column is not nullable but the val is) then you could try the following:

SELECT count (1)
    FROM a
    FULL OUTER JOIN b
        ON ( a.id = b.id
            AND a.val IS NOT DISTINCT FROM b.val )
    WHERE a.id IS NULL
        OR b.id IS NULL ;

Answer 2

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data):

(TABLE a EXCEPT TABLE b)
UNION ALL
(TABLE b EXCEPT TABLE a) ;

Or with EXISTS to return just a boolean value or a string with one of the 2 possible results:

SELECT CASE WHEN EXISTS (TABLE a EXCEPT TABLE b)
              OR EXISTS (TABLE b EXCEPT TABLE a)
            THEN 'different'
            ELSE 'same'
       END AS result ;

Tested at SQLfiddle

---

Also not the that EXCEPT removes duplicates (that should not be a worry if your tables have some PRIMARY KEY or UNIQUE constraint but it may be if you are comparing results of arbitrary queries that can potentially produce duplicate rows).

Another thing that EXCEPT keyword does is that it treats NULL values as identical, so if table A has a row with (1,2,NULL) and table B has a row with (1,2,NULL), the first query will not show these rows and the second query will return 'same' if the two tables have no other row.

If you want to count such rows as different, you could use a variation on gsiems' FULL JOIN answer, to get all the (different) rows:

SELECT *
FROM a NATURAL FULL JOIN b
WHERE a.some_not_null_column IS NULL 
   OR b.some_not_null_column IS NULL ;

and to get a yes/no answer:

SELECT CASE WHEN EXISTS
            ( SELECT *
              FROM a NATURAL FULL JOIN b
              WHERE a.some_not_null_column IS NULL 
                 OR b.some_not_null_column IS NULL
            )
            THEN 'different'
            ELSE 'same'
       END AS result ;

If all the columns of the two tables are not nullable, the two approaches will give identical answers.

Answer 3

You need Except clause Something like

SELECT * FROM first_table
EXCEPT
SELECT * FROM second_table

This return all rows from first table that are not in second table

Answer 4

Using except is the key to finding what is in table1 and not in table2 and vice versa and union the results. If they are identical then the count is zero.

;with CTE1 AS
(
  (
    select column1, column2 from Table1
    except
    select column1, column2 from Table2
  )
  UNION
  (
    select column1, column2 from Table2
    except
    select column1, column2 from Table1
  )
)
Select count(*) From CTE1  -- If identical count should be 0.

Answer 5

Workaround!

This is a workaround for a fast check and not a professional solution. It is only working with numerical columns as identifiers, else convert string columns to numeric (count the letters, convert to bytes or the like) if needed, and NULLs are no problem.

First unprofessional workaround: samples

In the end, since the queries took too long on a 1 Mio table to be compared with another with sometimes NULL values in the fields to care for as well, I have taken quite a few samples of chosen row numbers to test the difference of two tables roughly. I did not find any difference, thinking they are identical anyway.

Second a bit more professional workaround: sum() of columns

Though that sample seemed enough, I wanted to be sure, and summing up all numerical columns, I found differences!

SELECT sum(col1), sum(col2) FROM table1
UNION
SELECT sum(col1), sum(col2) FROM table2

Output is like:

sum(col1)	sum(col2)
11111345678	123456789101234
11111123456	123456789101234

This summing up of columns or the whole table is the fasted check, and it should usually already answer the main question and reveals more if you group the table by interesting attributes.

Checksum

It is a bit like the CHECKSUM solution in MySQL.

Mind: MySQL!!, not Postgresql:

CHECKSUM TABLE original_table, backup_table;

You might find better ways at How can I get a hash of an entire table in postgresql?.

The issue here is that it is just a Hash, information like in my case, when I could see from the sum of the col1 that there must be 0 or NULL values, gets lost.

Next step: find differences

Next step to find the differences is then to sort by col1 in both tables, or to group by - count() on col1 if it is not unique.

You might also know already which attributes might be the problem and check that, in my case, I new that some col1 might be 0. Summing 0 makes no sense, thus counting here, and the sum of the other column proves the difference:

SELECT count(col1), sum(col2) FROM table1 WHERE col1 = 0
UNION
SELECT count(col1), sum(col2) FROM table1 WHERE col1 = 0

count(col1)	sum(col2)
1234	123456789
345	6543210

A counter-query:

SELECT count(col1), sum(col2) FROM table1 WHERE col1 <> 0
UNION
SELECT count(col1), sum(col2) FROM table1 WHERE col1 <> 0

and after some time, you find the candidates with some luck or after a group by overview that you can check as well by subtracting the one from the other, ORDER BY difference DESC.

Perhaps run long-running queries now

But even if you know heavy scripts now to find the real differences in a full table check, you know at least that waiting for an hour or more makes sense at all. :)

Small hint: select into a new table

Small hint: if you run such a long-lasting query, consider indexing at first. Also, check whether you might better directly create a table from the sql results using

CREATE TABLE MY_TABLE AS SELECT ...

So that you have the results not just in the output.

Answer 6

If you do not care about one table being less than the other, but you only care about differences in the attributes:

Looking at the linked code you don't understand:

select count(*) from
(
select * From EmpDtl1
union
select * From EmpDtl2
)

The secret sauce is using union as opposed to union all. The former retains only distinct rows whereas the latter keeps duplicates (reference). In other words the nested querys says "give me all rows and columns from EmpDtl1 and in addition those from EmpDtl2 which are not already in EmpDtl1". The count of this subquery will be equal to the count of EmpDtl1 if and only if EmpDtl2 does not contribute any rows to the result i.e. the two tables are identical.

Alternatively, dump the tables in key sequence to two text files and use your comparison tool of choice.