Blog Post by Murat Yaşar


Posted on Sunday, June 21, 2015 10:47:11 AM and it has been read 4939 times since then.


Compare Tables Row By Row in PostgreSQL

Hello again,

I am sure this is not the only way of comparing data between two tables in PostgreSQL but I thought that sharing this info might help someone else out there.

This is just pure script and you do not need to install any third party software for doing this comparing job.

I also think that there must be other ways of doing this similar kind of job but this is my contribution. Please write your own as a comment.

While trying to solve a problem at work, one of my colleagues at the work office gave me the following script for comparing data between two similar tables whether or not they are matching. You can use the script for your own purpose by changing it. I will just give you some quick example to get you started.

It might be helpfull for you to compare tables and its data.

To demostrate this, I will create two similar table and populate them with some arbitrary data.

CREATE TABLE "Test"."TableA"
(
  "ID" bigserial NOT NULL,
  "Name" character varying,
  "ItemNo" bigint,
  CONSTRAINT "PKey_TableA" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);

CREATE TABLE "Test"."TableB"
(
  "ID" bigserial NOT NULL,
  "Name" character varying,
  "ItemNo" bigint,
  CONSTRAINT "PKey_TableB" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);

 

After creating tables I got the following screen from Pg-Admin IDE.

 

/BlogEntryImages/142/PostgreSQLDataCompare_01.png

 

Now, I am going to insert data into these newly created tables by using the following script.

 

INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'lorem',1);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'ipsum',2);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'dolor sit amet',3);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'consectetur',4);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'adipiscing',5);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'elit',6);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'sed do eiusmod',7);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'tempor incididunt',8);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'ut labore et dolore',9);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'magna aliqua',10);
INSERT INTO "Test"."TableA"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableA_ID_seq"'::regclass),'magna aliqua derte',11);

INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'lorem',1);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'ipsu m',2);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'dolor sit amet',3);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'consectetur',4);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'adipiscing',5);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'elit',6);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'sed doeiusmod',7);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'tempor incididunt',8);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'ut labore et dolora',9);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'magna aliqua',10);
INSERT INTO "Test"."TableB"("ID","Name","ItemNo") VALUES (nextval('"Test"."TableB_ID_seq"'::regclass),'magna aliqua derte',13);

 

It is time to reflect how this data compare will work.


SELECT * FROM ( SELECT "TableA"::text AS a FROM "Test"."TableA" ) x
LEFT JOIN (SELECT "TableB"::text AS a FROM "Test"."TableB" ) z ON x.a = z.a
WHERE z.a is null

When you execute above script, you will get the following result.

 

/BlogEntryImages/142/PostgreSQLDataCompare_03.png

 

It means we have 4 rows which do not contain the same data. The first column of result is coming from TableA and the second column of result is coming from TableB.

Rows in the second column contains no data because the data is not the same with the TableA.

If you execute the above script without using where clause you will get the following result. Null represents data mismatch between two tables for that specific row.

/BlogEntryImages/142/PostgreSQLDataCompare_05.png


SELECT * FROM ( SELECT "TableB"::text AS a FROM "Test"."TableB" ) x
LEFT JOIN ( SELECT "TableA"::text AS a FROM "Test"."TableA" ) z ON x.a = z.a
WHERE z.a is null

When you execute above script, you will get the following result.

 

/BlogEntryImages/142/PostgreSQLDataCompare_04.png
 

It means we have 4 rows which do not contain the same data. The first column of result is coming from TableB and the second column of result is coming from TableA.

Rows in the second column contains no data because the data is not the same with the TableB.

If you execute the above script without using where clause you will get the following result. Null represents data mismatch between two tables for that specific row.

 

/BlogEntryImages/142/PostgreSQLDataCompare_06.png


SELECT * FROM ( SELECT "TableA"::text AS a FROM "Test"."TableA" ) x
LEFT JOIN ( 
SELECT * FROM 
dblink('hostaddr=192.168.1.2 port=5432 dbname=nameofthedatabase user=username password=passw'::text,
' SELECT "CompanyGroup"::text as a FROM "ERP"."CompanyGroup" ' ) dblink( a text ) ) z ON x.a = z.a
WHERE z.a is null

Above script does the same thing but the only difference is dblink usage.

If you want to compare two similar tables not in the same database server but in different database servers then you can use this option.

Any comment which will make this entry more helpfull to the others is welcome.

Have a great day.


(In order to use this feature, you have to register.)


  • Daniel
    Daniel | Reply
    Monday, November 27, 2017 1:36:40 PM

    Excellent !!!

    It helped me a lot in my compare/sync tasks.

    You could use "FULL JOIN" to get all the differences in the same result.

    Thanks!!

     

     


    • Murat Yasar
      Murat Yasar | Reply
      Tuesday, November 28, 2017 5:32:30 AM

      I am glad to hear these.

      Thank you.

Tag Related Blog Entries

Countries Android Mobile Application

Saturday, April 23, 2016 0   844  

Some Helpful Links For Software Developers

Saturday, April 28, 2012 0   3526  

Show All Functions and Views In PostgreSQL By Using Select Statement And Querying pgCatalog

Saturday, February 18, 2012 0   1088  

Populate Nested TreeView In Asp.Net Using Common Table Expression In SQL Server

Sunday, May 01, 2011 0   3238   5

Npgsql Data Types And Equivalents

Monday, March 21, 2011 2   3181  

DataBinding GridView In Asp.NET Application By Using PostgreSQL as DataSource

Sunday, October 10, 2010 0   4170  

PostgreSQL - PL/pgSQL Little Code Snippets

Friday, October 08, 2010 3   1777  

LINQ

Friday, October 08, 2010 0   1366  

PostgreSQL 8.4.4 Data Types

Monday, September 27, 2010 0   1014  

Tuning, Optimizing, Increasing and Improving Performance of Asp.Net Application - Part III

Saturday, January 23, 2010 0   1515