PostgreSQL中的跨数据库查询 [英] Cross Database Query in PostgreSQL

问题描述

我正在尝试在Postgres中建立查询.我的背景是SQL Server，所以我遇到了一些语法上的挑战.我的查询需要在两个单独的服务器上命中两个单独的数据库.我需要在数据集之间进行联接.本质上，我在db1中有一个带有用户登录活动的表.每次用户登录网站都会添加一个条目.在db2上，我有一张带有购买表.对于每一天，我需要查看:有多少人登录，有多少名用户进行了购买.

我的表格如下:

Logins                Purchases
---------             ---------
ID                    User_ID
User_ID               Amount
LoginDate 

如果我的购买"表上有一个日期字段，这将很容易.但事实并非如此.因此，目前，我正在尝试以下操作:

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;

如何获取每天在Postgres中的登录和购买次数?谢谢！

PostgreSQL不支持跨数据库查询.大多数需要跨数据库"查询的人都会使用一个包含多个 schema 的数据库来获得查询.我不确定MS-SQL，但是MySQL的数据库"更像PostgreSQL的模式".

如果需要跨数据库查询，则必须使用DBLink或postgres-fdw.或者，您可以复制数据-查看Londiste，Slony-I或简单的cronjob.

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.

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:

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!

解决方案

Cross-database queries are not supported by PostgreSQL. Most people who want "cross-database" queries land up instead using a single database with multiple schema within it. I'm not sure about MS-SQL, but MySQL's "database"s are more like PostgreSQL "schema".

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.

这篇关于PostgreSQL中的跨数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！