Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA16819 for ; Fri, 24 Sep 1999 10:29:26 -0400 (EDT) (envelope-from rison@biochemistry.ucl.ac.uk) Received: from biochem.ucl.ac.uk (actually host bsmori1.biochem.ucl.ac.uk) by mail-b.bcc.ac.uk with SMTP (XT-PP) with ESMTP; Fri, 24 Sep 1999 15:28:29 +0100 Received: from bsmlx17.biochem.ucl.ac.uk (bsmlx17 [128.40.46.142]) by biochem.ucl.ac.uk (8.9.2/8.9.1) with ESMTP id PAA07615; Fri, 24 Sep 1999 15:28:35 +0100 (BST) Received: from localhost (rison@localhost) by bsmlx17.biochem.ucl.ac.uk (8.9.3/8.6.6) with ESMTP id PAA26771; Fri, 24 Sep 1999 15:32:51 +0100 X-Authentication-Warning: bsmlx17.biochem.ucl.ac.uk: rison owned process doing -bs Date: Fri, 24 Sep 1999 15:32:51 +0100 (BST) From: Stuart Rison X-Sender: rison@bsmlx17 To: "'pgsql-sql@hub.org'" cc: jt-kirkpatrick@mpsllc.com Subject: RE: [SQL] comparing 2 tables. . . In-Reply-To: <01BF06A4.7FFF3C60@Dan> Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset=X-UNKNOWN Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id KAB17151 On Fri, 24 Sep 1999, [iso-8859-2] Daniel Péder wrote: > try this, it should select what in tab1 is not in tab2 ( compared by pkeys ) > > select pkey.tab1 where pkey.tab1 not in ( pkey.tab2 ); erm... unless the 'pkey' is an operator of some kind in PG 6.5.x that I'm completely unaware of, I don't think that will work. Try: SELECT tab1.pkey FROM tab1 WHERE tab1.pkey NOT IN (SELECT tab2.pkey FROM tab2.pkey); or else (faster): SELECT tab1.pkey FROM tab1 WHERE NOT EXISTS (SELECT 1 FROM tab2 WHERE tab1.pkey=tab2.pkey); Finally, if you have version 6.5.x you could use MINUS: SELECT tab1.pkey FROM tab1 MINUS SELECT tab2.pkey FROM tab2; Can't vouch for the last one as I only have PG 6.4.2. HTH, S. > > -----Original Message----- > From: JT Kirkpatrick [SMTP:jt-kirkpatrick@mpsllc.com] > > 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 Stuart C. G. Rison Department of Biochemistry and Molecular Biology 6th floor, Darwin Building, University College London (UCL) Gower Street, London, WC1E 6BT, United Kingdom Tel. 0207 504 2303, Fax. 0207 380 7033 e-mail: rison@biochem.ucl.ac.uk