From: | "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov> |
---|---|
To: | "Mark Wimer" <mwimer(at)usgs(dot)gov> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: connecting /sharing tables across databases |
Date: | 2008-11-19 18:46:35 |
Message-ID: | 264855a00811191046j2ca029bbv704c57c63407c664@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Wimer | 2008-11-19 19:31:24 | Re: connecting /sharing tables across databases |
Previous Message | Mark Wimer | 2008-11-19 18:17:23 | Re: connecting /sharing tables across databases |