RE: [SQL] comparing 2 tables. . .

If you want to use 'minus'(just like oracle), in pg you should use 'except'
. ie:

select * from tab1
except
select * from tab2 ;

regards, omid.

>From: Stuart Rison <rison(at)biochemistry(dot)ucl(dot)ac(dot)uk>
>To: "'pgsql-sql(at)hub(dot)org'" <pgsql-sql(at)hub(dot)org>
>CC: jt-kirkpatrick(at)mpsllc(dot)com
>Subject: RE: [SQL] comparing 2 tables. . .
>Date: Fri, 24 Sep 1999 15:32:51 +0100 (BST)
>
>On Fri, 24 Sep 1999, [iso-8859-2] Daniel Pder 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 <your_table> tab1 WHERE tab1.pkey NOT IN (SELECT
>tab2.pkey FROM <your_other_table> tab2.pkey);
>
>or else (faster):
>
>SELECT tab1.pkey FROM <your_table> tab1 WHERE NOT EXISTS (SELECT 1 FROM
><your_other_table> tab2 WHERE tab1.pkey=tab2.pkey);
>
>Finally, if you have version 6.5.x you could use MINUS:
>
>SELECT tab1.pkey FROM <your_table> tab1
>MINUS
>SELECT tab2.pkey FROM <your_other_table> 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(at)mpsllc(dot)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(at)biochem(dot)ucl(dot)ac(dot)uk
>
>
>************
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com