Postgres: Compare all fields from two tables and report back differences

Question

I have two tables that have the same fields. I want to compare each field of one table with the fields of the other table and report back any difference in the data.

I would like to do this in a dynamic way with out having to hard code the name of the fields in my query. Is this even possible or should I find a solution to this by using Python?

Example of tables:

 TABLE 1 TABLE 2 FIELD1 : VALUE1 FIELD1 : VALUE1 FIELD2 : VALUE2 FIELD2 : VALUE2 FIELD3 : VALUE3 FIELD3 : VALUE3xxx

Query goes through all the fields and then says "A ha" Field3 do not match, I must show this to the end user"

Generally we ask that you search for similar or identical answers before posting. Google is also a good idea. In this case, a quick search locates pgfoundry.org/projects/pg-comparator ... — Craig Ringer
– Craig Ringer, Commented Aug 23, 2015 at 11:05

Community · Accepted Answer · 2017-05-23 12:06:16Z

0

You have to create a Dinamic Sql and put it inside a function

Dynamic sql in postgres

For start you need contruct a sql to compare field name

And to get a table field names

select column_name from information_schema.columns where table_name='table 1';

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Aug 22, 2015 at 22:22

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

1 Comment

George Mastrokostas Over a year ago

Yeah, I need to read upon Dynamic SQL. I started approaching the solution to my little problem by using Python. If it gets too messy/dirty I will drop it though. Thank you for your pointer by the way. Dynamic SQL is a good topic to know and I had forgotten it even existed.

Collectives™ on Stack Overflow

Postgres: Compare all fields from two tables and report back differences

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related