ivxHQnit

1-- CHECK DIFF BEWEEN TWO DATABASES (A and B) 
2-----------------------------------------------
3
4/*********************************************
5 * 1.0 CREATE THE APPROPRIATE CONFIGURATION
6 *********************************************/
7
8-- create a new database AB (from terminal)
9createdb AB -T A
10-- connect to AB (sql)
11alter schema public rename to A;
12create schema public;
13-- export/import the B database (by default in the public schema)(from terminal)
14pg_dump B > /tmp/B.sql
15cat /tmp/B.sql | psql -d AB
16-- remame public schema to B (sql)
17alter schema public rename to B;
18-- recreate the public schema
19create schema public;
20
21/*********************************************
22 * 2.0 RUN THE SCRIPT
23 *********************************************/
24
25
26DO
27$do$
28declare
29	v_table_name varchar;
30	v_column_names varchar;
31	v_cnt_a_not_in_b int;
32	v_cnt_b_not_in_a int;
33begin
34	/*create table*/
35	  DROP TABLE IF EXISTS TABLE_DIFFS;
36      CREATE TABLE TABLE_DIFFS
37        (
38            ID SERIAL,
39            TABLE_NAME varchar,
40            CNT_IN_A_NOT_IN_B int,
41            CNT_IN_B_NOT_IN_A int,
42            CNT int
43        );
44	FOR v_table_name, v_column_names IN
45	    select table_name , string_agg(column_name,',') column_names from 
46	    (
47	         select
48	               table_name,
49	               concat('"',column_name,'"') column_name,
50	               table_schema
51	         from information_schema.columns  
52	         where 1=1 
53	         and table_schema in ('a')
54	         and column_name not in ('create_date', 'write_date', 'write_uid', 'create_uid')
55	     )A
56	     group by table_name
57    loop
58      raise notice 'processing table %',v_table_name;  
59      EXECUTE format('select count(*) from 
60                        (
61				SELECT %s FROM a.%I
62				EXCEPT
63				SELECT %s FROM b.%I
64			)A', v_column_names, v_table_name, v_column_names, v_table_name)
65						INTO v_cnt_a_not_in_b;
66	  EXECUTE format('select count(*) from 
67                        (
68				SELECT %s FROM b.%I
69				EXCEPT
70				SELECT %s FROM a.%I
71			)A', v_column_names, v_table_name, v_column_names, v_table_name) 
72						INTO v_cnt_b_not_in_a;
73      insert into TABLE_DIFFS(TABLE_NAME,CNT_IN_A_NOT_IN_B, CNT_IN_B_NOT_IN_A, CNT) 
74      values(v_table_name, v_cnt_a_not_in_b, v_cnt_a_not_in_b, v_cnt_b_not_in_a+v_cnt_a_not_in_b );
75    END LOOP;
76  
77end;
78$do$;
79
80/*
81select * from TABLE_DIFFS where cnt>0