Thread: Comparing tables in different db's
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
Phillip F Jansen wrote: >I would like to know if there is a way to compare the data of tables in >different databases. Forexample I have table in db1 and exactly the >same table in db2. Is it possible to see if the contents of the two >tablesare 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 WHEREid = '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
>>>>>>>>>>>>>>>>>> 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.
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.999999999345677.999999999345679.000000001345679.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';
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
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.
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