Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 23ADE9FB45F for ; Wed, 16 May 2007 04:00:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 27595-09 for ; Wed, 16 May 2007 03:59:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 284739FB40F for ; Wed, 16 May 2007 04:00:03 -0300 (ADT) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Wed, 16 May 2007 08:45:38 +0200 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Wed, 16 May 2007 09:00:01 +0200 Message-Id: <464AC809.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Wed, 16 May 2007 08:59:53 +0200 From: "Bart Degryse" To: Subject: Re: SQL Query Validate Records Multiple Tables - HelpNeeded References: <1179235046.748509.130630@y80g2000hsf.googlegroups.com><11792683 46.212283.25370@w5g2000hsg.googlegroups.com> In-Reply-To: <1179268346.212283.25370@w5g2000hsg.googlegroups.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part361137F9.0__=" X-imss-version: 2.046 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/136 X-Sequence-Number: 28416 --=__Part361137F9.0__= Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I'm working on something similar at the moment. Don't know about you, but = I have an additional problem: I have to deal with rather large datasets = (>20000 recs per query) to be moved between the two databases (Oracle and = PostgreSQL in my case). After doing lots of performance test, for me the = conclusion was that dblink doesn't really work, dbi-link works but has way = too much overhead and is way to slow. So finally I installed plperlu and wrote some functions of my own and = these work at a very acceptable speed. Transferring data between two = different Oracle databases was on average not faster than between Oracle = and PostgreSQL. >>> Rodrigo De Le=F3n 2007-05-16 0:32 >>> On May 15, 8:17 am, Paul251 wrote: > Hello... > > I am trying to validate a asset number (10 Characters) from one table > to another table. Problem is they are in two different DB's and > haven't done that before? > > Basically trying to take record 1 from Table 1/DB1 and validate it > against record 1 in Table 2/DB2 if the record doesn't exist in Table 2/ > DB2 update the flag to N in Table 1/DB1..... Seems easy but been > working on it for about a week now no luck.... :-( I have the mostly > the same colums in both tables but Table 2/DB2 is the host table I > need to verify from.... > > Does anyone have any help or code to help me do this?? > > Thanks See: http://www.postgresql.org/docs/faqs.FAQ.html#item4.17=20 https://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib= /dblink/doc/query?rev=3D26230=20 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match --=__Part361137F9.0__= Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Description: HTML

I'm working on something similar at the moment. Don't know about you, = but I have an additional problem: I have to deal with rather large = datasets (>20000 recs per query) to be moved between the two = databases (Oracle and PostgreSQL in my case). After doing lots of = performance test, for me the conclusion was that dblink doesn't really = work, dbi-link works but has way too much overhead and is way to slow.

So finally I installed plperlu and wrote some functions of my own and = these work at a very acceptable speed. Transferring data between two = different Oracle databases was on average not faster than between Oracle = and PostgreSQL.

>>> Rodrigo De Le=F3n <rdeleonp@gmail.co= m> 2007-05-16 0:32 >>>
On May 15, 8:17 am, Paul251 = <savethe...@gmail.com> wrote:
> Hello...
>
> I am = trying to validate a asset number (10 Characters) from one table
> = to another table. Problem is they are in two different DB's and
> = haven't done that before?
>
> Basically trying to take record = 1 from Table 1/DB1 and validate it
> against record 1 in Table 2/DB2 = if the record doesn't exist in Table 2/
> DB2 update the flag to = N in Table 1/DB1..... Seems easy but been
> working on it for = about a week now no luck.... :-( I have the mostly
> the same colums = in both tables but Table 2/DB2 is the host table I
> need to = verify from....
>
> Does anyone have any help or code to help = me do this??
>
> Thanks

See:

http://www.postgresql.org/doc= s/faqs.FAQ.html#item4.17

https://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/cont= rib/dblink/doc/query?rev=3D26230

---------------------------= (end of broadcast)---------------------------
TIP 9: In versions below = 8.0, the planner will ignore your desire to
&nbs= p; choose an index scan if your joining column's datatypes do = not
match

--=__Part361137F9.0__=--