Checking whether two tables have identical content in PostgreSQL

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.

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 ;