pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Joining tables in two different databases?
3+ messages / 3 participants
[nested] [flat]

* Joining tables in two different databases?
@ 2007-08-09 02:22  Michael Goldner <MGoldner@agmednet.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Michael Goldner @ 2007-08-09 02:22 UTC (permalink / raw)
  To: pgsql-admin

Is it possible to access tables in two different databases, running on the
same server, from within psql?

I have a table in a database that is "linked" to a table in another database
(using XA transactions in Jboss).  I'd like to do some archiving at the
database level, which requires that for every record in db1.table1, I need
to archive the corresponding record in db2.table1.

I seem to recall using some other rdbms in the past that allowed one to
prefix the table name with the dbname.  Can this be done in postgres?

If not, it seems that I'll need to do some sort of "COPY" operation to
export the primary keys to a temporary table, import it into the second db,
and then perform my archive based on the table.  Does this seem appropriate?

Otherwise, I could write the archive procedure in Java, using the XA
datasource.  I was hoping to avoid the overhead and complexity involved
versus a succinct sql statement.

Thanks!

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Joining tables in two different databases?
@ 2007-08-09 19:08  Chris Hoover <revoohc@gmail.com>
  parent: Michael Goldner <MGoldner@agmednet.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Chris Hoover @ 2007-08-09 19:08 UTC (permalink / raw)
  To: Michael Goldner <MGoldner@agmednet.com>; +Cc: pgsql-admin

On 8/8/07, Michael Goldner <MGoldner@agmednet.com> wrote:
>
>  Is it possible to access tables in two different databases, running on
> the
> same server, from within psql?
>
Take a look at dblink in the contrib area.  I think it will give you what
you are looking for.

Chris

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Joining tables in two different databases?
@ 2007-08-11 02:22  Julio Cesar Sánchez González <knowhow@sistemasyconectividad.com.mx>
  parent: Chris Hoover <revoohc@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Julio Cesar Sánchez González @ 2007-08-11 02:22 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: Michael Goldner <MGoldner@agmednet.com>; pgsql-admin

El jue, 09-08-2007 a las 15:08 -0400, Chris Hoover escribió:
> On 8/8/07, Michael Goldner <MGoldner@agmednet.com> wrote:
>         Is it possible to access tables in two different databases,
>         running on the
>         same server, from within psql?
>         

yes it is, with dblink.

follow next link:

http://www.postgresql.org/docs/faqs.FAQ.html

4.17) How do I perform queries using multiple databases?
There is no way to query a database other than the current one. Because
PostgreSQL loads database-specific system catalogs, it is uncertain how
a cross-database query should even behave.

contrib/dblink allows cross-database queries using function calls. Of
course, a client can also make simultaneous connections to different
databases and merge the results on the client side.


>         
>         
> Take a look at dblink in the contrib area.  I think it will give you
> what you are looking for.
> 
> Chris

-- 
Regards,

Julio Cesar Sánchez González
www.sistemasyconectividad.com.mx
blog: http://darkavngr.blogspot.com

---
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2007-08-11 02:22 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-09 02:22 Joining tables in two different databases? Michael Goldner <MGoldner@agmednet.com>
2007-08-09 19:08 ` Chris Hoover <revoohc@gmail.com>
2007-08-11 02:22   ` Julio Cesar Sánchez González <knowhow@sistemasyconectividad.com.mx>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox