Thread: Accessing a database form another database

I've got some GIS tables that I want to use across several projects and
currently I've got each project in a separate database. So say I've got
two databases, one for the Spacely Sprookets project and one for the
Cogswell Cogs contract, but I want to access my WorldMap tables in my
GIS database to make maps for both of these projects.  How do I
reference a table that's in another database? Or should I organize my
PostgreSQL data differently?

Thanks,
- Bill Thoen

On Mon, Aug 10, 2009 at 10:48:10AM -0600, Bill Thoen wrote:
> I've got some GIS tables that I want to use across several projects
> and  currently I've got each project in a separate database. So say
> I've got  two databases, one for the Spacely Sprookets project and
> one for the  Cogswell Cogs contract, but I want to access my
> WorldMap tables in my  GIS database to make maps for both of these
> projects.  How do I  reference a table that's in another database?
> Or should I organize my PostgreSQL data differently?

That last is much easier.  Create a schema each for Spacely and
Cogswell, then one for WorldMap.

http://www.postgresql.org/docs/current/static/sql-createschema.html

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Bill Thoen wrote:
> I've got some GIS tables that I want to use across several projects and
> currently I've got each project in a separate database. So say I've got
> two databases, one for the Spacely Sprookets project and one for the
> Cogswell Cogs contract, but I want to access my WorldMap tables in my
> GIS database to make maps for both of these projects.  How do I
> reference a table that's in another database? Or should I organize my
> PostgreSQL data differently?

You can use dblink for this purpose:


http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html


--
Guy Rouillier

Just a thought, but would it not be possible to add a trigger written in python that makes a connection to another database and does what it needs to? I have not done this, but if it is possible it should solve your problem and one that I have. It should allow updates and selects.

I am sure the docs will tell you how to do perl or python based triggers and if you can use external modules in them...if you can, it would be a rather straight forward python app that accesses the GIS tables using a DB connector (postgres or mysql or what ever).

Since I have not read much (more or less nothing) about triggers I could be way off base and entirely wrong.

Thanks,
Kobus