Re: Accessing tables in one database from another

On Fri, 2007-11-16 at 15:07 -0800, Mary Anderson wrote:
> Hi,
>     I originally created a database called memdev.  Then I created a 
> second database called inputdev, and put in some files of stuff I wanted 
> to process and then read into memdev.  I am not having much luck finding 
> out how to do this from the postgres manual.   try to do this with
> 
> (SELECT * FROM inputdev.jpn_references
> 
> but i am just told that there is no schema named inputdev.  (So I should 
> have put these tables in a schema, not a database!)

There is no native communication between databases.  So yes, a separate
schema would do.

> Question 1.  Can I login to memdev and do a SELECT on a table in 
> inputdev?  If so, what is the syntax?

You can install the contrib module, dblink, and use that.

> Question 2. How do I export my inputdev tables into memdev if I can't 
> access them from memdev?  The manual for pg_dump and pg_restore only 
> tells me about restoring into an empty database.

dblink again

It might indeed be easier to use a separate schema in the same database.

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
 You're nearly out of time!   http://www.lfix.co.uk/disappearance.html


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.