首页 / 知识

关于sql：可以使用PostgreSQL执行跨数据库查询吗？

2023-04-14 08:36:00

Possible to perform cross-database queries with PostgreSQL?

我将基于以下错误消息(以及此Google结果)猜测答案为"否"，但是是否仍然可以使用PostgreSQL执行跨数据库查询？

1
2
3

databaseA=# SELECT * FROM databaseB.public.someTableName;
ERROR: cross-DATABASE REFERENCES are NOT implemented:
"databaseB.public.someTableName"

我正在处理一些跨两个数据库分区的数据，尽管数据实际上是在两个数据库之间共享的(一个数据库中的userid列来自另一个数据库中的users表)。我不知道为什么这些是两个独立的数据库而不是模式，但是请放心...

注意：就像原始的问询者所暗示的那样，如果您要在同一台计算机上设置两个数据库，则可能要创建两个模式-在这种情况下，您不需要任何特殊的查询即可在它们之间进行查询。

从9.3开始更新

现在，您可以使用新的postgres_fdw(外部数据包装器)连接到任何Postgres数据库(本地或远程)中的表。

请注意，还有其他流行数据源的外部数据包装器。目前，只有postgres_fdw和file_fdw是Postgres官方发行版的一部分。

9.3版之前的原始答案

该功能不是默认PostgreSQL安装的一部分，但是您可以添加它。它称为dblink。

我从未使用过它，但是它与PostgreSQL的其余部分一起维护和分发。如果您使用的是Linux发行版随附的PostgreSQL版本，则可能需要安装一个名为postgresql-contrib的软件包。

在得出关于跨数据库查询的相同结论之前，我已经碰到了这一点。我最终要做的是使用模式划分表空间，这样我可以将表分组，但仍然可以查询所有表。

dblink()-在远程数据库中执行查询

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.

很好的例子之一：

1
2
3
4
5
6
7

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;

注意：我提供此信息以供将来参考。提神

_{只需添加更多信息。}

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常见问题

是的，您可以使用DBlink(仅适用于postgresql)和DBI-Link(允许使用外部跨数据库查询器)和TDS_LInk，从而允许针对MS SQL Server运行查询。

我曾经使用DB-Link和TDS-link取得了巨大的成功。

如果性能很重要，并且大多数查询都是只读的，则建议将数据复制到另一个数据库。尽管这似乎是不必要的数据重复，但如果需要索引可能会有所帮助。

这可以通过简单的插入触发器来完成，该触发器依次调用dblink以更新另一个副本。也有成熟的复制选项(例如Slony)，但这是不合时宜的。

我已经检查并尝试使用dblink和postgres_fdw在2个不同数据库的2个表之间创建外键关系，但没有结果。

阅读了其他人对此的反馈，例如在这里和这里以及在其他一些来源中，看来目前尚无办法：

dblink和postgres_fdw实际上使人们能够连接和查询其他数据库中的表，这是标准Postgres所无法实现的，但是它们不允许在不同数据库中的表之间建立外键关系。

如果有人需要更多有关如何进行跨数据库查询的示例，下面的示例将清理每个具有该数据库的数据库上的databasechangeloglock表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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(*) > 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
$$

执行数据库查询消息错误