Thread: comparing 2 tables. . .

can anyone help please??

we have two tables in our database that *should* have the exact same number 
of records, with identical values in their corresponding primary keys (pk 
same in both tables, int4).  for this example, table t1 with primary key 
pk, and table t2 with primary key pk.  after about 3 months of use and a 
crash last night i see that the two tables do NOT have the same number of 
records -- one is missing some that it should have.  is there a way i can 
compare the two tables and select ONLY the records that do NOT exist in the 
2nd table??  if i could identify the records (and the pk) then i could 
manually insert them.  each table has over 50,000 records so it is not 
feasable to review each record to find them manually (which i tried last 
night. . .).

we are struggling this morning -- any help you could provide quickly would 
be greatly appreciated!

jt kirkpatrick / mps

Hello,

You can try this, (but i think it may take a long time )      1 : Find which tuples are in t1 and not in t2select
distinctt1.pk from t1 where t1.pk not in (select t2.pk from t2);      2 : Find which tuples are in t2 and not in
t1selectdistinct t2.pk from t2 where t2.pk not in (select t1.pk from t1);In the case that you only want to known the
references

If you want to rebuilt your table  after analyze you can make this :   select distinct t1.pk  into t1b from t1 where
t1.pknot in (select t2.pk
 
from t2);   select distinct t2.pk  into t2b from t2 where t2.pk not in (select t1.pk
from t1); After your analyze you can insert tuples of table t1b into table t1 and
tuples of table t2b into table t2.

Regards

--
Basier Eric  e-mail : basier@ipgp.jussieu.fr
http://geoscope.ipgp.jussieu.fr
IPGP Observatoires Sismologie-Volcanologie
4,Place Jussieu 75005 Paris Tour 24-14 4eme Etage Tel 01 44 27 38 96