Take the 2-minute tour ×
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.

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
    
You can use EXCEPT, check this question: An efficient way to compare two large data sets in SQL –  ypercube Jul 29 at 10:05

3 Answers 3

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

share|improve this answer
    
There could be some more efficient method, not sure. –  ypercube Jul 29 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 at 15:29
    
The EXCEPT query is a beaut! –  Erwin Brandstetter Jul 30 at 1:30

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 at 14:40
    
@Faheem Mitha -- that is correct –  gsiems Jul 29 at 15:03
    
@FaheemMitha: And to check for complete duplicates use a NATURAL FULL JOIN b. –  Erwin Brandstetter Jul 30 at 1:39
    
@ErwinBrandstetter what is a complete duplicate? –  Faheem Mitha Jul 30 at 13:32
1  
@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 at 13:38

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
1  
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 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.