PostGreSql query for equality between two tables

Question

I have two tables A & B and i would like to have a query that : return TRUE ONLY if the two tables are the same (i mean every lines in A are present in B & vice versa, no matter the line order)

I have use the keyword EXCEPT but it doesnt work in many cases

Thanks for your help.

Answer 1

select * from tablea except all select * from tableb

returns all rows from tablea that do not exist in tableb.

doing it the other way around

select * from tableb except all select * from tablea

returns all rows in tableb that do not exist in tablea.

so, now we can:

select count(*) from ( select * from tablea except all select * from tableb ) x;

to get number of "bad" rows in tablea, and:

select count(*) from ( select * from tableb except all select * from tablea ) x;

to get count of "bad" rows in tableb.

tables are the same if both counts are 0. and since neither count can be less than zero, then we can test if sum of the counts is 0:

select 0 = ( select count(*) from ( select * from tablea except all select * from tableb ) x ) + ( select count(*) from ( select * from tableb except all select * from tablea ) x );