Thread: how to merge a table from another DB

I have a bit of a dilemma, I'm fairly new to PG, and not quite sure how
to do this:
I have a DB that has been basically 'forked' - a copy of the original
was made for a handful of users to play with without messing with the
live DB. Now, they would like to know if the changes they have made to
the forked db can be merged back in to the live one. A change to one
column of every row in a certain table. How can I update just that field
in every row of the db without harming the existing data already in that
table?

thanks in advance.

-Todd

> the forked db can be merged back in to the live one. A change to one
> column of every row in a certain table. How can I update just that field
> in every row of the db without harming the existing data already in that
> table?

Would something like this work?

    UPDATE table1
    set table1.columna = table2.columna
    from table2
    where table1.keyfield = table2.keyfield;
--
Mike Nolan

On Tue, Feb 17, 2004 at 11:55:55 -0700,
  "Hought, Todd" <Todd.Hought@echostar.com> wrote:
> I have a bit of a dilemma, I'm fairly new to PG, and not quite sure how
> to do this:
> I have a DB that has been basically 'forked' - a copy of the original
> was made for a handful of users to play with without messing with the
> live DB. Now, they would like to know if the changes they have made to
> the forked db can be merged back in to the live one. A change to one
> column of every row in a certain table. How can I update just that field
> in every row of the db without harming the existing data already in that
> table?

To change the data in a column you can just use an update command.
Alter table can be used to change constraints or to change the column
tpye. (The latter is usually done by adding a new column, converting
the old values, dropping the old column, renaming the new column
back to the original name, and then fixing any constraints.)

On Tue, 17 Feb 2004, Hought, Todd wrote:

> I have a bit of a dilemma, I'm fairly new to PG, and not quite sure how
> to do this:
> I have a DB that has been basically 'forked' - a copy of the original
> was made for a handful of users to play with without messing with the
> live DB. Now, they would like to know if the changes they have made to
> the forked db can be merged back in to the live one. A change to one
> column of every row in a certain table. How can I update just that field
> in every row of the db without harming the existing data already in that
> table?

If you're running 7.3 or above, look at merging the two databases under
one with different schemes and permissions.  then you'd be able to do
updates pretty easily.  Otherwise, you're gonna have to export / import
your data from one machine to the other with pg_dump / psql or pg_restore

Could you do...

UPDATE mytable SET mycolumn =
(
 SELECT myothercolumn
 FROM myothertable
 WHERE myothertable.primarykey = mytable.primarykey
)

Not sure how this works in PostgreSQL, but I do it all the time in
Oracle (note only one table definition in subselect)


thanks!

 ~ Troy Campano ~



On Tue, 2004-02-17 at 13:55, Hought, Todd wrote:
> I have a bit of a dilemma, I'm fairly new to PG, and not quite sure how
> to do this:
> I have a DB that has been basically 'forked' - a copy of the original
> was made for a handful of users to play with without messing with the
> live DB. Now, they would like to know if the changes they have made to
> the forked db can be merged back in to the live one. A change to one
> column of every row in a certain table. How can I update just that field
> in every row of the db without harming the existing data already in that
> table?
>
> thanks in advance.
>
> -Todd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match