<p>I'm going to guess that the answer is "no" based on the below error message (and <a href="http://archives.postgresql.org/pgsql-sql/2004-08/msg00076.php" rel="noreferrer">this Google result</a>), but is there anyway to perform a cross-database query using PostgreSQL?</p>

<pre><code>databaseA=# select * from databaseB.public.someTableName;
ERROR:  cross-database references are not implemented:
 "databaseB.public.someTableName"
</code></pre>

<p>I'm working with some data that is partitioned across two databases although data is really shared between the two (userid columns in one database come from the <code>users</code> table in the other database). I have no idea why these are two separate databases instead of schema, but c'est la vie...</p>

[{'answer_id': 46336, 'author': 'stimms', 'author_id': 361, 'author_profile': 'https://Stackoverflow.com/users/361', 'pm_score': 5, 'selected': False, 'text': '<p>I have run into this before an came to the same conclusion about cross database queries as you.  What I ended up doing was using schemas to divide the table space that way I could keep the tables grouped but still query them all.  </p>\n'}, {'answer_id': 46448, 'author': 'Neall', 'author_id': 619, 'author_profile': 'https://Stackoverflow.com/users/619', 'pm_score': 8, 'selected': True, 'text': '<p><em>Note: As the original asker implied, if you are setting up two databases on the same machine you probably want to make two <a href="https://www.postgresql.org/docs/current/static/ddl-schemas.html" rel="noreferrer">schemas</a> instead - in that case you don\'t need anything special to query across them.</em></p>\n\n<h2><code>postgres_fdw</code></h2>\n\n<p>Use <a href="https://www.postgresql.org/docs/current/postgres-fdw.html" rel="noreferrer"><code>postgres_fdw</code></a> (foreign data wrapper) to connect to tables in any Postgres database - local or remote.</p>\n\n<p>Note that there are <a href="http://wiki.postgresql.org/wiki/Foreign_data_wrappers" rel="noreferrer">foreign data wrappers for other popular data sources</a>. At this time, only <code>postgres_fdw</code> and <code>file_fdw</code> are part of the official Postgres distribution.</p>\n\n<h3>For Postgres versions before 9.3</h3>\n\n<p>Versions this old are no longer supported, but if you need to do this in a pre-2013 Postgres installation, there is a function called <a href="https://www.postgresql.org/docs/current/dblink.html" rel="noreferrer"><code>dblink</code></a>.</p>\n\n<p>I\'ve never used it, but it is maintained and distributed with the rest of PostgreSQL. If you\'re using the version of PostgreSQL that came with your Linux distro, you might need to install a package called postgresql-contrib.</p>\n'}, {'answer_id': 59389, 'author': 'dpavlin', 'author_id': 1081, 'author_profile': 'https://Stackoverflow.com/users/1081', 'pm_score': 2, 'selected': False, 'text': "<p>If performance is important and most queries are read-only, I would suggest to replicate data over to another database. While this seems like unneeded duplication of data, it might help if indexes are required.</p>\n\n<p>This can be done with simple on insert triggers which in turn call dblink to update another copy. There are also full-blown replication options (like Slony) but that's off-topic.</p>\n"}, {'answer_id': 113337, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<p>Yes, you can by using DBlink (postgresql only) and DBI-Link (allows foreign cross database queriers) and TDS_LInk which allows queries to be run against MS SQL server.</p>\n\n<p>I have used DB-Link and TDS-link before with great success.</p>\n'}, {'answer_id': 2784520, 'author': 'Esteban Küber', 'author_id': 34813, 'author_profile': 'https://Stackoverflow.com/users/34813', 'pm_score': 4, 'selected': False, 'text': '<p><sub>Just to add a bit more information.</sub></p>\n<blockquote>\n<p>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.</p>\n<p>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.</p>\n</blockquote>\n<p><a href="http://wiki.postgresql.org/wiki/FAQ#How_do_I_perform_queries_using_multiple_databases.3F" rel="noreferrer">PostgreSQL FAQ</a></p>\n'}, {'answer_id': 27054140, 'author': 'Manwal', 'author_id': 2236219, 'author_profile': 'https://Stackoverflow.com/users/2236219', 'pm_score': 6, 'selected': False, 'text': '<h1><a href="http://www.postgresql.org/docs/current/interactive/dblink.html" rel="nofollow noreferrer">dblink()</a> -- executes a query in a remote database</h1>\n<blockquote>\n<p>dblink executes a query (usually a SELECT, but it can be any SQL\nstatement that returns rows) in a remote database.</p>\n<p>When two text arguments are given, the first one is first looked up as\na persistent connection\'s name; if found, the command is executed on\nthat connection. If not found, the first argument is treated as a\nconnection info string as for dblink_connect, and the indicated\nconnection is made just for the duration of this command.</p>\n</blockquote>\n<p>one of the good example:</p>\n<pre><code>SELECT * \nFROM   table1 tb1 \nLEFT   JOIN (\n   SELECT *\n   FROM   dblink(\'dbname=db2\',\'SELECT id, code FROM table2\')\n   AS     tb2(id int, code text);\n) AS tb2 ON tb2.column = tb1.column;\n</code></pre>\n<p>Note: I am giving this information for future reference. <a href="https://stackoverflow.com/questions/4678862/joining-results-from-two-separate-databases">Reference</a></p>\n'}, {'answer_id': 55653353, 'author': 'Haroldo_OK', 'author_id': 679240, 'author_profile': 'https://Stackoverflow.com/users/679240', 'pm_score': 1, 'selected': False, 'text': "<p>In case someone needs a more involved example on how to do cross-database queries, here's an example that cleans up the <code>databasechangeloglock</code> table on every database that has it:</p>\n\n<pre><code>CREATE EXTENSION IF NOT EXISTS dblink;\n\nDO \n$$\nDECLARE database_name TEXT;\nDECLARE conn_template TEXT;\nDECLARE conn_string TEXT;\nDECLARE table_exists Boolean;\nBEGIN\n    conn_template = 'user=myuser password=mypass dbname=';\n\n    FOR database_name IN\n        SELECT datname FROM pg_database\n        WHERE datistemplate = false\n    LOOP\n        conn_string = conn_template || database_name;\n\n        table_exists = (select table_exists_ from dblink(conn_string, '(select Count(*) &gt; 0 from information_schema.tables where table_name = ''databasechangeloglock'')') as (table_exists_ Boolean));\n        IF table_exists THEN\n            perform dblink_exec(conn_string, 'delete from databasechangeloglock');\n        END IF;     \n    END LOOP;\n\nEND\n$$\n</code></pre>\n"}, {'answer_id': 57008050, 'author': 'Rocckk', 'author_id': 11248397, 'author_profile': 'https://Stackoverflow.com/users/11248397', 'pm_score': 2, 'selected': False, 'text': '<p>I have checked and tried to create a foreign key relationships between 2 tables in 2 different databases using both <em>dblink</em> and <em>postgres_fdw</em> but with no result.</p>\n\n<p>Having read the other peoples feedback on this, for example <a href="https://www.postgresql.org/message-id/flat/CADVWZZL-yARvY9srTy2HM2n8HxW%3DeYhpEJJjzYor7E_5tUYRqw%40mail.gmail.com" rel="nofollow noreferrer">here</a> and <a href="https://dba.stackexchange.com/questions/212649/create-foreign-key-on-foreign-table-in-postgresql">here</a> and in some other sources it looks like there is no way to do that currently:</p>\n\n<p>The <em>dblink</em> and <em>postgres_fdw</em> indeed enable one to connect to and query tables in other databases, which is not possible with the standard Postgres, but they do not allow to establish foreign key relationships between tables in different databases.</p>\n'}, {'answer_id': 66078827, 'author': 'Kind Contributor', 'author_id': 887092, 'author_profile': 'https://Stackoverflow.com/users/887092', 'pm_score': 2, 'selected': False, 'text': '<p>see <a href="https://www.cybertec-postgresql.com/en/joining-data-from-multiple-postgres-databases/" rel="nofollow noreferrer">https://www.cybertec-postgresql.com/en/joining-data-from-multiple-postgres-databases/</a> [published 2017]</p>\n<p>These days you also have the option to use <a href="https://prestodb.io/" rel="nofollow noreferrer">https://prestodb.io/</a></p>\n<p>You can run SQL on that PrestoDB node and it will distribute the SQL query as required. It can connect to the same node twice for different databases, or it might be connecting to different nodes on different hosts.</p>\n<p>It does not support:</p>\n<pre><code>DELETE\nALTER TABLE\nCREATE TABLE (CREATE TABLE AS is supported)\nGRANT\nREVOKE\nSHOW GRANTS\nSHOW ROLES\nSHOW ROLE GRANTS\n</code></pre>\n<p>So you should only use it for SELECT and JOIN needs. Connect directly to each database for the above needs. (It looks like you can also INSERT or UPDATE which is nice)</p>\n<p>Client applications connect to PrestoDB primarily using <a href="https://prestodb.io/docs/current/installation/jdbc.html" rel="nofollow noreferrer">JDBC</a>, but other types of connection are possible including a <a href="https://prestodb.io/docs/current/installation/tableau.html" rel="nofollow noreferrer">Tableu compatible web API</a></p>\n<p>This is an open source tool governed by the Linux Foundation and Presto Foundation.</p>\n<blockquote>\n<p>The founding members of the Presto Foundation are: Facebook, Uber,\nTwitter, and Alibaba.</p>\n<p>The current members are: Facebook, Uber, Twitter, Alibaba, Alluxio,\nAhana, Upsolver, and Intel.</p>\n</blockquote>\n'}]
['https://Stackoverflow.com/questions/46324', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4249/']