Re: Comparing two tables of different database
| От | Nicholas I |
|---|---|
| Тема | Re: Comparing two tables of different database |
| Дата | |
| Msg-id | e13d0d10904300312j5ec7baa1l3ae91724f6f5dbf8@mail.gmail.com обсуждение |
| Ответ на | Comparing two tables of different database (Nicholas I <nicholas.domnic.i@gmail.com>) |
| Ответы |
Re: Comparing two tables of different database
Re: Comparing two tables of different database |
| Список | pgsql-sql |
Hi All,
For example,
There are two database. database1 and database 2;
database1 has a table called pr_1 with the columns, id,name and time.
database2 has a table called sr_1 with the_columns id,name and time.
i would like to find out the differences that is, find the names that are not in sr_1 but in pr_1.
we can achieve this by the query,
select name from sr_1 where name not in (select name from pr_1);
the above query will work in case of two tables in the same database.
But the problem is, these two tables are in different database. i did not understand about the dblink.
is there any exaples on dblink. can we do it without using dblink.
-Nicholas I
For example,
There are two database. database1 and database 2;
database1 has a table called pr_1 with the columns, id,name and time.
database2 has a table called sr_1 with the_columns id,name and time.
i would like to find out the differences that is, find the names that are not in sr_1 but in pr_1.
we can achieve this by the query,
select name from sr_1 where name not in (select name from pr_1);
the above query will work in case of two tables in the same database.
But the problem is, these two tables are in different database. i did not understand about the dblink.
is there any exaples on dblink. can we do it without using dblink.
-Nicholas I
On Thu, Apr 30, 2009 at 9:07 AM, Joshua Tolley <eggyknap@gmail.com> wrote:
On Thu, Apr 30, 2009 at 08:20:02AM +1000, Adam Ruth wrote:For all but the strictest definition of "identical", that won't work.
> The simple answer is to pg_dump both tables and compare the output with
> diff.
> Other than that, I think you'll need a custom program.
Tables may easily contain the same information, in different on-disk
order, and pg_dump will most likely give the data to you in an order
similar to its ordering on disk.
Something like a COPY (<query>) TO <file>, where <query> includes an
ORDER BY clause, might give you a suitable result from both tables, on
which you could then take a checksum.
- Josh / eggyknap
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
iEYEARECAAYFAkn5HQoACgkQRiRfCGf1UMPcagCfQDRa2bXPRjgSuVsrFYTnGTTC
rhoAnAlGwp0vSKd2uspyFvxCTfugG6Yh
=LO6r
-----END PGP SIGNATURE-----
В списке pgsql-sql по дате отправления: