Search code examples
postgresqlexcept

Using EXCEPT and flagging column differences

What Im looking to do is select data from a postgres table, which does not appear in another. Both tables have identical columns, bar the use of boolean over Varchar(1) but the issue is that the data in those columns do not match up.

I know I can do this with a SELECT EXCEPT SELECT statement, which I have implemented and is working.

What I would like to do is find a method to flag the columns that do not match up. As an idea, I have thought to append a character to the end of the data in the fields that do not match.

For example if the updateflag is different in one table to the other, I would be returned '* f' instead of 'f'

SELECT id, number, "updateflag" from dbc.person
EXCEPT
SELECT id, number, "updateflag":bool from dbg.person;

Should I be joining the two tables together, post executing this statement to identify the differences, from whats returned?

I have tried to research methods to implement this but have no found anything on the topic

Solution

  • I prefer a full outer join for this

    select *
from dbc.person p1
  full join dbg.person p2 on p1.id = p2.id
where p1 is distinct from p2;

    The id column is assumed the primary key column that "links" the two tables together.

    This will only return rows where at least one column is different.

    If you want to see the differences, you could use a hstore feature

    select hstore(p1) - hstore(p2) as columns_diff_p1, 
       hstore(p2) - hstore(p1) as columns_diff_p2
from dbc.person p1
  full join dbg.person p2 on p1.id = p2.id
where p1 is distinct from p2;