blocks|key|1772331|text|在我对跨数据库查询得出与您相同的结论之前，我已经遇到过这种情况。我最终所做的是使用模式来划分表空间，这样我就可以保持表的分组，但仍然可以查询所有表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1772332|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1772425|text|如果性能很重要，并且大多数查询都是只读的，我建议将数据复制到另一个数据库。虽然这似乎是不必要的重复数据，但如果需要索引，它可能会有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1772426|这可以使用简单的on+insert触发器来完成，这些触发器反过来调用dblink来更新另一个副本。也有成熟的复制选项(如Slony)，但这是离题的。|1772427|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1772541|text|可以，您可以使用DBlink+(仅限postgresql)和DBI-Link+(允许外部跨数据库查询器)和TDS_LInk+(允许在MS+SQL+server上运行查询)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1772542|我以前使用过DB-Link和TDS-link，非常成功。|1772543|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1772584|text|如果有人需要一个关于如何进行跨数据库查询的更复杂的示例，这里有一个示例，它将清理每个数据库上的databasechangeloglock表：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1772585|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+%7C%7C+database_name;

++++++++table_exists+=+(select+table_exists_+from+dblink(conn_string,+'(select+Count(*)+>+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
$$|code-block|syntax|javascript|1772586|entityMap^0|1B|L|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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:

<pre><code>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
$$
</code></pre>

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?

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

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...

Possible to perform cross-database queries with PostgreSQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

根据下面的错误消息(和)，我猜答案是“否”，但是有没有办法使用PostgreSQL执行跨数据库查询呢？databaseA=# select * from databaseB.public.someTableName;ERROR:  cross-database references are not implemente...

问是否可以使用PostgreSQL执行跨数据库查询？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否可以使用PostgreSQL执行跨数据库查询？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否可以使用PostgreSQL执行跨数据库查询？
EN