Home 2017 December PostgreSQL: Get the data difference between two Tables

PostgreSQL: Get the data difference between two Tables

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a simple way to get the data difference between two tables in PostgreSQL.

Recently, one of the junior folk asked me about this and I prepared the query using FULL OUTER JOIN.

Using this solution, you can get the missing data for both tables in the form of differences.

Create two sample tables:

Insert sample data into both tables:

Check the difference of data:

Result:

Anvesh Patel
PostgreSQL: Create a Copy of Table or Create a Duplicate TablePostgreSQL: Replace String data in all the Columns of a Table
Comments: 4
  1. Abhi
    December 3, 2017 at 4:26 pm

    Can I use left join to do the same thing here?

    ReplyCancel
  2. ikomrad
    January 18, 2019 at 8:09 pm

    Is this really the difference between 2 tables?
    Table 1
    a = 1
    b =2
    c = 3

    Table 2
    a = 5
    b = 2
    c= 3

    If I wanted to write a query that told me that A in Table 1 is not the same value as A in Table 2, how would I got about it?

    ReplyCancel
  3. Namdev Rathod
    April 22, 2019 at 1:02 pm

    Hi,

    Thanks for your post. I have one quick question here.

    I want to check difference between two tables from two different database like dev and prod.

    and if difference found from dev then update same to prod.
    only structure not data.

    Please help me with suitable solution and query.

    ReplyCancel
  4. leebert
    June 5, 2019 at 3:47 pm

    Food for thought … here’s a different way to look for inter-table diffs ..

    CREATE INDEX tbl_A_idx0 ON tbl_A (id) ;
    CREATE INDEX tbl_A_idx1 ON tbl_A ( ( tbl_A )::text) ;

    CREATE INDEX tbl_B_idx0 ON tbl_B (id) ;
    CREATE INDEX tbl_B_idx1 ON tbl_B ( ( tbl_B )::text) ;

    SELECT
    ( tbl_A)::text = ( tbl_B)::text

    FROM tbl_A a
    JOIN tbl_B b
    ON a.ID = b.ID
    AND ( tbl_A)::text ( tbl_B)::text ;

    (a more exotic approach, creating a data type … you have to ensure that tbl_A & tbl_B are both of type tbl_AB_type )

    CREATE TYPE tbl_AB_type ( id int, fld1 bigint, fld2 text ) ;
    CREATE INDEX tbl_A_idx1 ON tbl_A ( ( tbl_A )::tbl_AB_type ) ;
    CREATE INDEX tbl_B_idx1 ON tbl_B ( ( tbl_B )::tbl_AB_type ) ;

    SELECT
    ( tbl_A)::tbl_AB_type = ( tbl_B)::tbl_AB_type as _bool
    , ( tbl_A) AS _row_A
    , ( tbl_B) AS _row_B
    INTO TEMP TABLE _tmp_tbl
    FROM tbl_A a
    JOIN tbl_B b
    ON a.ID = b.ID
    AND ( tbl_A)::tbl_AB_type ( tbl_B)::tbl_AB_type ;

    SELECT (_row_A).id, (_row_B).fld1
    FROM _tmp_tbl
    WHERE ( _row_A) = (_row_B) ;

    … and so on …

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

December 3, 2017 4 Comments PostgreSQL, , , , , , , , , , , , , ,