Compare two tables in postgres

Question

I have two tables with identical definition:

create tabA (
user_id int,
contact boolean,
promote boolean
);

create tabB (
user_id int,
contact boolean,
promote boolean
);

I want to compare two columns contact and promote and see if there is any discrepancy in row data. For example:

row from tabA: 1,T,T
row from tabB: 1,T,F

So there is discrepancy now I want to catch that and select only those rows where they are not equal.

You would have to define what happens with NULL values, which are obviously allowed according to your table definitions ... — Erwin Brandstetter
– Erwin Brandstetter, Commented May 26, 2015 at 23:58

Dondi Michael Stroma · Accepted Answer · 2015-05-26 22:10:13Z

1

SELECT * FROM tabA, tabB 
WHERE tabA.user_id = tabA.user_id 
AND (
 tabA.contact != tabB.contact 
 OR
 tabA.promote != tabB.promote
);

answered May 26, 2015 at 22:10

Dondi Michael Stroma

4,84021 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tushar Purohit Over a year ago

you got it !! appreciate your help

Erwin Brandstetter · Accepted Answer · 2015-05-27 00:05:52Z

1

As long as there can be NULL values, you need to use null-safe operators:

SELECT user_id, a.contact AS a_contact, a.promote AS a_promote 
              , b.contact AS b_contact, b.promote AS b_promote 
FROM   tabA a
JOIN   tabB b USING (user_id)
WHERE  a.contact IS DISTINCT FROM b.contact OR
       a.promote IS DISTINCT FROM b.promote;

answered May 27, 2015 at 0:05

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Comments

user330315 · Accepted Answer · 2018-06-22 20:09:16Z

1

Another option is to use Postgres' record comparison capability:

select *
from taba a
  full join tabb b using (user_id)
where a is distinct from b;

answered Jun 22, 2018 at 20:09

user330315

Comments

Sergey · Accepted Answer · 2018-06-22 19:54:46Z

0

To find differences in table content you should compare 3 column's values of next query:

select (
        select count(*) from (
            select * from a
            union
            select * from b
        ) m
    ) merged,
    (select count(*) from a) in_a,
    (select count(*) from b) in_b;

If value in merged column is not equal to value in columns in_a and in_b then the content of table a and b has at least one difference.

answered Jun 22, 2018 at 19:54

Sergey

1

Collectives™ on Stack Overflow

Compare two tables in postgres

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related