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.