Re: connecting /sharing tables across databases

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