Skip site navigation (1) Skip section navigation (2)

Re: connecting /sharing tables across databases

From: Mark Wimer <mwimer(at)usgs(dot)gov>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: connecting /sharing tables across databases
Date: 2008-11-19 19:31:24
Message-ID: OF6ACFE894.0EA17D35-ON85257506.006A0461-85257506.006B3EF9@usgs.gov (view raw or flat)
Thread:
Lists: pgsql-novice
Thanks Sean - I guess my initial (negative) reaction to a single database 
was that "it's different" from what I am used to doing. But there does not 
appear to be a reason NOT to use a single database at the moment. Now to 
go and move stuff out of the public schema.... which several different 
sites recommended. 

Mark



On Wed, Nov 19, 2008 at 1:17 PM, Mark Wimer <mwimer(at)usgs(dot)gov> wrote:

In MSSQL, I have a dozen or so databases that rely on some shared tables 
(states, species lists, for example). I put the shared tables in a db 
called Common. To query them, mssql allows: 

select statename, stateabbrev from common.dbo.statelist S inner join 
mytable on S.stateabbrev = mytable.stateabbrev 

I'm not asking for PostgreSQL to be more like mssql, but I would like to 
share some lookup tables among applications & databases. In looking 
through the help, it appears from the "Schemas" help page (
http://www.postgresql.org/docs/8.3/static/ddl-schemas.html) that 
PostgreSQL uses a single database per connection (pgAdmin client, web 
client). Is there a best practice or recommendation page for how to handle 
this sharing of a common lookup table? Or a help page I missed that 
discusses it? 

Right now I see my options as: 
a) put all applications for which I use PostgreSQL into one database, but 
different schemas. 

The standard solution is (a). 
 

b) use a client tool to make a separate connection and create a join 
afterwards 

Postgres has other options.  You can look at the dblink contrib module to 
connect between postgres databases on the same server.  Also, a project 
(perl-based) called DBI-Link allows you to query from other databases 
(mysql, mssql, etc.) directly from within postgresql. 

If you have total control of the database, though, I would say stick with 
(a).

Sean


In response to

pgsql-novice by date

Next:From: Obe, ReginaDate: 2008-11-19 20:20:11
Subject: Re: ssl tunneling in postgres 8.1
Previous:From: Sean DavisDate: 2008-11-19 18:46:35
Subject: Re: connecting /sharing tables across databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group