PostgreSQL 中的跨数据库查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23789822/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cross Database Query in PostgreSQL
提问by user70192
I am trying to build a query in Postgres. My background is in SQL Server so, I'm having some syntactical challenges. My query needs to hit two seperate databases on two separate servers. I need to do a join between the datasets. Essentially, I have a table with user login activity in db1. A entry gets added each time a user logs into the website. On db2, I have a table with purchases. For each day, I need to see: how many people logged in and how many of the users that logged in made a purchase.
我正在尝试在 Postgres 中构建查询。我的背景是 SQL Server，所以我遇到了一些语法上的挑战。我的查询需要在两个单独的服务器上访问两个单独的数据库。我需要在数据集之间进行连接。本质上，我在 db1 中有一个包含用户登录活动的表。每次用户登录网站时都会添加一个条目。在 db2 上，我有一张购买的表。对于每一天，我需要查看：有多少人登录以及有多少登录用户进行了购买。
My tables look like this:
我的表是这样的：
Logins Purchases
--------- ---------
ID User_ID
User_ID Amount
LoginDate
This would be easy if my Purchases table had a date field on it. But it doesn't. So, currently, I'm trying the following:
如果我的 Purchases 表上有一个日期字段，这将很容易。但事实并非如此。因此，目前，我正在尝试以下操作：
SELECT
// Somehow get the number of logins for the given day here
// Somehow I need to get the number of purchases for the given day here
TO_CHAR(TO_TIMESTAMP((LoginDate/1000) - 14400), 'MM/DD/YYYY') AS the_day
FROM
db1.Logins AS t1,
db2.Purchases as t2
GROUP BY the_day
ORDER BY the_day;
How do I get the number of logins and purchases for a each day in Postgres? Thank you!
如何在 Postgres 中获得每天的登录和购买次数？谢谢！
回答by Craig Ringer
Cross-database queries are not supported by PostgreSQL. Most people who want "cross-database" queries land up instead using a single database with multiple schemawithin it. I'm not sure about MS-SQL, but MySQL's "database"s are more like PostgreSQL "schema".
PostgreSQL 不支持跨数据库查询。大多数想要“跨数据库”查询的人都使用其中包含多个模式的单个数据库。我不确定 MS-SQL，但 MySQL 的“数据库”更像是 PostgreSQL“模式”。
If you require cross-database queries you must use DBLink or postgres-fdw. Or you can replicate the data - look into Londiste, Slony-I, or a simple cronjob.
如果您需要跨数据库查询，则必须使用 DBLink 或 postgres-fdw。或者您可以复制数据 - 查看 Londiste、Slony-I 或简单的 cronjob。
