Possible to perform cross-database queries with PostgreSQL

Question

I m going to guess that the answer is  no  based on the below error message  and this Google result   but is there anyway to perform a cross-database query using PostgreSQL   databaseA   select   from databaseB public someTableName  ERROR   cross-database references are not implemented    databaseB public someTableName    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 users table in the other database   I have no idea why these are two separate databases instead of schema  but c est la vie

User · Answer

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.

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.

User · Answer

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   I have used DB-Link and TDS-link before with great success

User · Answer

Note  As the original asker implied  if you are setting up two databases on the same machine you probably want to make two schemas instead - in that case you don t need anything special to query across them   postgres fdw  Use postgres fdw  foreign data wrapper  to connect to tables in any Postgres database - local or remote   Note that there are foreign data wrappers for other popular data sources  At this time  only postgres fdw and file fdw are part of the official Postgres distribution   For Postgres versions before 9 3  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 dblink   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

User · Answer

see https   www cybertec-postgresql com en joining-data-from-multiple-postgres-databases   published 2017  These days you also have the option to use https   prestodb io  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  It does not support  DELETE ALTER TABLE CREATE TABLE  CREATE TABLE AS is supported  GRANT REVOKE SHOW GRANTS SHOW ROLES SHOW ROLE GRANTS  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  Client applications connect to PrestoDB primarily using JDBC  but other types of connection are possible including a Tableu compatible web API This is an open source tool governed by the Linux Foundation and Presto Foundation   The founding members of the Presto Foundation are  Facebook  Uber  Twitter  and Alibaba  The current members are  Facebook  Uber  Twitter  Alibaba  Alluxio  Ahana  Upsolver  and Intel

User · Answer

In case someone needs a more involved example on how to do cross-database queries  here s an example that cleans up the databasechangeloglock table on every database that has it   CREATE EXTENSION IF NOT EXISTS dblink   DO     DECLARE database name TEXT  DECLARE conn template TEXT  DECLARE conn string TEXT  DECLARE table exists Boolean  BEGIN     conn template    user myuser password mypass dbname         FOR database name IN         SELECT datname FROM pg database         WHERE datistemplate   false     LOOP         conn string   conn template    database name           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            IF table exists THEN             perform dblink exec conn string   delete from databasechangeloglock            END IF           END LOOP   END

User · Answer

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

User · Answer

dblink   -- executes a query in a remote database  dblink executes a query  usually a SELECT  but it can be any SQL statement that returns rows  in a remote database  When two text arguments are given  the first one is first looked up as a persistent connection s name  if found  the command is executed on that connection  If not found  the first argument is treated as a connection info string as for dblink connect  and the indicated connection is made just for the duration of this command   one of the good example  SELECT    FROM   table1 tb1  LEFT   JOIN      SELECT      FROM   dblink  dbname db2   SELECT id  code FROM table2      AS     tb2 id int  code text     AS tb2 ON tb2 column   tb1 column   Note  I am giving this information for future reference  Refrence

User · Answer

I have checked and tried to create a foreign key relationships between 2 tables in 2 different databases using both dblink and postgres fdw but with no result   Having read the other peoples feedback on this  for example here and here and in some other sources it looks like there is no way to do that currently   The dblink and postgres fdw 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

User · Answer

Just to add a bit more information   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   PostgreSQL FAQ

[sql] Possible to perform cross-database queries with PostgreSQL?

Examples related to sql

Examples related to postgresql