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';