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(at)lfix(dot)co(dot)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.