I would like to know if there is a way to compare the data of tables in
different databases. For example I have table in db1 and exactly the
same table in db2. Is it possible to see if the contents of the two
tables are exactly the same?
For example if the table(db1.customer) has a column surname and the
field has a value of "Testing" and in db2.customer the exact same row
has a value "Testong" is it possible to actually know that there is a
difference ? I don't actually have to know what the actual differences
are , I must just know that there is a difference.

Thank you

Phillip


Fri, 10 Aug 2001 10:59:22 +0200
Phillip F Jansen <pfj@ucs.co.za>
Comparing tables in different db's
Phillip F Jansen wrote:
  >I would like to know if there is a way to compare the data of tables in
  >different databases. For example I have table in db1 and exactly the
  >same table in db2. Is it possible to see if the contents of the two
  >tables are exactly the same?
  >For example if the table(db1.customer) has a column surname and the
  >field has a value of "Testing" and in db2.customer the exact same row
  >has a value "Testong" is it possible to actually know that there is a
  >difference ? I don't actually have to know what the actual differences
  >are , I must just know that there is a difference.

You can't do it inside PostgreSQL.  However, this shell script will do it:

  psql -d db1 -tc "SELECT surname FROM customer WHERE id = 'xxx'" >db1.out
  psql -d db2 -tc "SELECT surname FROM customer WHERE id = 'xxx'" >db2.out
  if ! diff db1.out db2.out >/dev/null
  then
     echo Databases differ
  fi
  rm db[12].out

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If ye abide in me, and my words abide in you, ye shall
      ask what ye will, and it shall be done unto you."     
                                      John 15:7 



Fri, 10 Aug 2001 11:25:22 +0100
"Oliver Elphick" <olly@lfix.co.uk>
Re: Comparing tables in different db's 


>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

> I would like to know if there is a way to compare the data of tables in
> different databases. For example I have table in db1 and exactly the
> same table in db2. Is it possible to see if the contents of the two
> tables are exactly the same?

I use pg_dump for my tests.  Example

pg_dump -a -t table_name db1 > db1_dump.out
pg_dump -a -t table_name db2 > db2_dump.out

Then you can use diff db1_dump.out db2_dump.out

I hope this helps

Darren

> For example if the table(db1.customer) has a column surname and the
> field has a value of "Testing" and in db2.customer the exact same row
> has a value "Testong" is it possible to actually know that there is a
> difference ? I don't actually have to know what the actual differences
> are , I must just know that there is a difference.



Fri, 10 Aug 2001 13:39:56 GMT
Darren Johnson <djohnson@greatbridge.com>
Re: Comparing tables in different db's
Darren Johnson wrote:

> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
>
> > I would like to know if there is a way to compare the data of tables in
> > different databases. For example I have table in db1 and exactly the
> > same table in db2. Is it possible to see if the contents of the two
> > tables are exactly the same?
>
> I use pg_dump for my tests.  Example
>
> pg_dump -a -t table_name db1 > db1_dump.out
> pg_dump -a -t table_name db2 > db2_dump.out
>
> Then you can use diff db1_dump.out db2_dump.out

(1) The output contains the OID and the owner, so I guess it won't work without stripping comments first?

(2) It (still) doesn't work if you have datetime columns with more than two digits in the miliseconds field (see below).

Yeah, I guess this means that the usual backup strategy doesn't work either....  :-(


    --- Allan.


test=# create table test (a datetime);
CREATE
test=# insert into test values ('2001-08-10 23:04:12.3456');
INSERT 12760275 1
test=# insert into test values ('2001-08-10 23:04:12.345678');
INSERT 12760276 1
test=# insert into test values ('2001-08-10 23:04:12.3456789');
INSERT 12760277 1
test=# insert into test values ('2001-08-10 23:04:12.345678901234567890');
INSERT 12760278 1
test=# select EXTRACT(MICROSECONDS FROM a) from test;
    date_part
------------------
 345599.999999999
 345677.999999999
 345679.000000001
 345679.000000001
(4 rows)

bash-2.04$ pg_dump -a -t test test > /tmp/test.dmp
bash-2.04$ cat /tmp/test.dmp
--
-- Selected TOC Entries:
--
--
-- Data for TOC Entry ID 1 (OID 12760265)
--
-- Name: test Type: TABLE DATA Owner: allane
--


\connect - postgres
-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'test';

\connect - allane
COPY "test"  FROM stdin;
2001-08-10 23:04:12.35+01
2001-08-10 23:04:12.35+01
2001-08-10 23:04:12.35+01
2001-08-10 23:04:12.35+01
\.
\connect - postgres
-- Enable triggers
UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = 'test';




Fri, 10 Aug 2001 23:14:46 +0100
Allan Engelhardt <allane@cybaea.com>
Re: Comparing tables in different db's
Allan Engelhardt wrote:


> (1) The output contains the OID and the owner, so I guess it won't work without stripping comments first?
> 
I was using an older version of PostgreSQL which doesn't have
the comments, and it looks like I'll need to make the OID/owner
comments an option in pg_dump, once I get the further along
in the changes I am working on.  In the mean time you can try
something like..
pg_dump -a -t table_name db1|egrep -v "\(OID|Owner" > db1_dump.out
but this is a hack to strip the offending comments, and
wouldn't work in every situation.

> (2) It (still) doesn't work if you have datetime columns with more than two digits in the miliseconds field (see below).
> 
I'm not sure about this one, I need to do more investigation here.  BTW what platform/OS
are you using?

Darren

 


Sat, 11 Aug 2001 22:32:59 -0400
Darren Johnson <djohnson@greatbridge.com>
Re: Comparing tables in different db's
Darren Johnson wrote:

> Allan Engelhardt wrote:
>
> > (2) It (still) doesn't work if you have datetime columns with more than two digits in the miliseconds field (see below).
> >
> I'm not sure about this one, I need to do more investigation here.  BTW what platform/OS
> are you using?

PostgreSQL 7.1.2-4PGDG, Linux 2.4.7 on i686 SMP.

Allan.


Sun, 12 Aug 2001 19:48:50 +0100
Allan Engelhardt <allane@cybaea.com>
Re: Comparing tables in different db's

Justin Clift wrote:


> If you finalise this into a decent procedure (and/or scripts), then
> would you mind contributing them?  I can place them on the
> techdocs.postgresql.org website as a start.
> 

Not at all, I plan to contribute any/all work I am
involved with.  This would be part of validating and
testing replication, but unfortunately it could be a
while before we get to that stage in the current
version of the PostgreSQL.  :(

Thanks,

Darren


Sun, 12 Aug 2001 17:13:44 -0400
Darren Johnson <djohnson@greatbridge.com>
Re: Re: Comparing tables in different db's