Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
1  
You can use EXCEPT, check this question: An efficient way to compare two large data sets in SQL – ypercubeᵀᴹ Jul 29 '14 at 10:05
up vote 12 down vote accepted

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.

share|improve this answer
    
This method appears to have the advantage that one can compare a subset of the columns. Is that correct? – Faheem Mitha Jul 29 '14 at 14:40
    
@Faheem Mitha -- that is correct – gsiems Jul 29 '14 at 15:03
    
@FaheemMitha: And to check for complete duplicates use a NATURAL FULL JOIN b. – Erwin Brandstetter Jul 30 '14 at 1:39
    
@ErwinBrandstetter what is a complete duplicate? – Faheem Mitha Jul 30 '14 at 13:32
2  
@FaheemMitha: Both rows have the same values in every row. Note that NULL values are never considered equal. So this works best with all columns defined NOT NULL. a NATURAL FULL JOIN b is shorthand for a FULL JOIN b USING (<list of all columns in common>). – Erwin Brandstetter Jul 30 '14 at 13:38

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.

share|improve this answer
    
There could be some more efficient method, not sure. – ypercubeᵀᴹ Jul 29 '14 at 10:16
    
@FaheemMitha you can use this to compare fewer columns than all. Just use SELECT <column_list> FROM a instead of TABLE a – ypercubeᵀᴹ Jul 29 '14 at 15:29
    
The EXCEPT query is a beaut! – Erwin Brandstetter Jul 30 '14 at 1:30

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.

share|improve this answer
2  
This will not detect the case when EmpDtl2 has less rows than EmpDtl1 and all existing rows do exist in EmpDtl1. – a_horse_with_no_name Jul 30 '14 at 13:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.