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!
