pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Goldner <MGoldner@agmednet.com>
To: pgsql-admin@postgresql.org
Subject: Joining tables in two different databases?
Date: Wed, 8 Aug 2007 22:22:50 -0400
Message-ID: <C2DFF594.138D2%mgoldner@agmednet.com> (raw)

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!

view thread (3+ messages)  latest in thread

Message-ID: <C2DFF594.138D2%mgoldner@agmednet.com>
Permalink:  ../C2DFF594.138D2%25mgoldner@agmednet.com/
Also on:    postgresql.org/message-id/C2DFF594.138D2%mgoldner@agmednet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: MGoldner@agmednet.com
  Subject: Re: Joining tables in two different databases?
  In-Reply-To: <C2DFF594.138D2%mgoldner@agmednet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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