Re: How to move data from 1 database to another? - Mailing list pgsql-general

dblink is your friend...

rms74=# select dblink_connect('dbname=bms75 port=5474');
 dblink_connect
----------------
 OK
(1 row)
rms74=# create table doesitwork as select * from dblink('select * from
current_downloads'::text) as t1(eid integer,st timestamptz);
SELECT
rms74=# select * from doesitwork
rms74-# ;
  eid  |              st
-------+-------------------------------
 11892 | 2003-07-14 14:12:56.202592-04
 12590 | 2003-09-11 11:36:22.94156-04
 14998 | 2003-10-20 14:12:58.428304-04
(3 rows)
rms74=# insert into doesitwork select * from dblink('select * from
current_downloads'::text) as t1(eid integer,st timestamptz);
INSERT 0 3
rms74=# select * from doesitwork;
  eid  |              st
-------+-------------------------------
 11892 | 2003-07-14 14:12:56.202592-04
 12590 | 2003-09-11 11:36:22.94156-04
 14998 | 2003-10-20 14:12:58.428304-04
 11892 | 2003-07-14 14:12:56.202592-04
 12590 | 2003-09-11 11:36:22.94156-04
 14998 | 2003-10-20 14:12:58.428304-04
(6 rows)
rms74=#

Robert Treat

On Tue, 2004-05-11 at 12:28, Thomas LeBlanc wrote:
> Actually, the database db2 has all ready been created, plus the table names
> are the same, so I can not use pg_dump and restore.
>
> INSERT INTO db1.public.tables SELECT * FROM db2.public.table1
>
> Thanks,
> Thomas
>
>
> >From: "scott.marlowe" <scott.marlowe@ihs.com>
> >To: Thomas LeBlanc <thomasatiem@hotmail.com>
> >CC: <pgsql-general@postgresql.org>
> >Subject: Re: [GENERAL] How to move data from 1 database to another?
> >Date: Tue, 11 May 2004 10:13:22 -0600 (MDT)
> >
> >On Tue, 11 May 2004, Thomas LeBlanc wrote:
> >
> > > I have 2 databases. I want to move data from table table1 in database
> >db1 to
> > > table2 in db2.
> > >
> > > When I query 1 table from another database, I get a cross-database
> > > references are not implemented.
> > >
> > > server1% psql db1
> > > emdata=# select * from db2.public.table1;
> > > ERROR:  Cross-database references are not implemented
> > >
> > >
> > > What can I do to get the data into another database?
> >
> >You can usually use pg_dump to accomplish such tasks.  It's not something
> >likely to be implemented any time soon now that schemas have been
> >implemented.  If you have no great reason to have two seperate databases
> >versus two schemas, you may want to investigate using schemas in the
> >future.
> >
> >For now, you can do something like:
> >
> >pg_dump -d db1 -t table1 |psql db2
> >
> >then psql and do
> >
> >insert into table2 (select * from table1);
> >
> >
>
> _________________________________________________________________
> FREE pop-up blocking with the new MSN Toolbar – get it now!
> http://toolbar.msn.com/go/onm00200415ave/direct/01/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL