Postgres Pro
Downloads
Home   >   mailing lists

Re: Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements - Mailing list pgsql-general

From Chris Curvey
Subject Re: Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
Date
Msg-id CADfwSsDE+iej2LdC=R_n8XoGv3tvcPdkBo+TmXs=6vpbxzCSBg@mail.gmail.com
Whole thread Raw
In response to Re: Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements  (Khangelani Gama <kgama@argility.com>)
Responses Re: Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements  (Khangelani Gama <kgama@argility.com>)
List pgsql-general
Tree view
I think the convention on this list is to bottom-post.

On Fri, Mar 28, 2014 at 7:59 AM, Khangelani Gama <kgama@argility.com> wrote:

Hi Chris

 

I did replace foo and bar tables with my tables, in db2 I have table foo(the one to be updated) and the table bar(the one which has data I got from db1)

 

From: ccurvey@gmail.com [mailto:ccurvey@gmail.com] On Behalf Of Chris Curvey
Sent: Friday, March 28, 2014 1:50 PM
To: Khangelani Gama
Cc: pgsql
Subject: Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements

 

 

 

On Fri, Mar 28, 2014 at 5:56 AM, Khangelani Gama <kgama@argility.com> wrote:

Hi Chris or anyone who can help

 

When I try this just below, it complains about foo , saying “ERROR: schema “foo” does not exist.” I got stuck on this error for a while now but still trying to see why but still no luck so far. If you have something please help

 

 

begin;

 

update foo

set br_desc = bar.br_desc

, br_active = bar.br_active

(rest of columns)

where foo.br_cde = bar.br_cde;

 

 

you need to replace "foo" and "bar" with the names of your tables.


I'm stumped.  Here's the test that I just ran on both 9.2 and 8.4, and I'm not getting an error:

drop table if exists foo;
drop table if exists bar;

create table foo (a int, b int);
create table bar (a int, b int);

insert into foo values (1, 1);
insert into foo values (2, 2);

insert into bar values (1, 3);
insert into bar values (4, 4);

update foo
set b = bar.b
from bar
where foo.a = bar.a;

select * from bar; 

Perhaps if you want to privately send me the exact statement you are using, I can take quick look and see if there's something obvious.

pgsql-general by date:

Previous
From: Rémi Cura
Date:
Subject: Re: Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
Next
From: Khangelani Gama
Date:
Subject: Re: Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements