Thread
-
Re: How to move data from 1 database to another?
Thomas LeBlanc <thomasatiem@hotmail.com> — 2004-05-11T16:28:24Z
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/
-
Re: How to move data from 1 database to another?
Nicholas Barr <nicky@chuckie.co.uk> — 2004-05-11T16:38:05Z
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 Yes in fact you can. Use pg_dump to dump the whole database. Then just do a data only pg_restore (--data-only is the command line switch). Bear in mind if the restore goes wrong then you are going to have to dropdb/createdb and then run in the schema again. I hope you have that schema in a file. Nick
-
Re: How to move data from 1 database to another?
scott.marlowe <scott.marlowe@ihs.com> — 2004-05-11T16:42:28Z
Oh, then just edit the dump to point to the right table and don't bother with the insert into part. On Tue, 11 May 2004, 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 >
-
Re: How to move data from 1 database to another?
Karsten Hilbert <karsten.hilbert@gmx.net> — 2004-05-11T16:44:35Z
> 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. Sure you can: dropdb db2 createdb db2 ... > >pg_dump -d db1 -t table1 |psql db2 Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
-
Re: How to move data from 1 database to another?
Jonathan Bartlett <johnnyb@eskimo.com> — 2004-05-11T16:54:02Z
> 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. Yes you can, just use pg_dump with the -a flag, and then just use psql with redirection rather than restore. Jon
-
Re: How to move data from 1 database to another?
Robert Treat <xzilla@users.sourceforge.net> — 2004-05-11T21:06:15Z
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