Blog Post by Murat Yaşar

1. Blog
2. Compare Tables Row By Row in PostgreSQL
3. Technical

---

Posted on Sunday, June 21, 2015 10:47:11 AM and it has been read 14538 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.

 

 

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.

 

 

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.

---

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.

 

 

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.

 

---

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.

---

---