Cross Database Query in PostgreSQL

Question

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!

PostgreSQL does not have any native support for connecting two databases together, so this is a lot more than a "syntactical challenge". You will need to look into a tool such as "dblink" or a "foreign data wrapper", or find a way of copying the data between servers or combining it from separate queries in your application. — IMSoP, May 21 '14 at 17:31
Yep - Foreign Data Wrappers are what you want, but why on earth do you have users in a different table from purchases? I can't see how that makes any sense — Richard Huxton, May 21 '14 at 18:54
@RichardHuxton Maybe the users have a single sign on for multiple applications, but each application has its own data, with separate backups, restrictions, etc. It's not the only way to design such a system, but it's not completely nonsensical. — IMSoP, May 21 '14 at 19:00
@IMSoP but surely you'd replicate the user information to each database then? Or have some way of reaching from one to the other from the beginning anyway. — Richard Huxton, May 21 '14 at 19:06
Since this looks more like a statistical assignment I would suggest you to do the following: With a nightjob push the data from the 2 server on another database and have all the data in one server and then preform the statistics there. Only use full doh if you gonna need that for more then just this one analisys of data. Otherwise Foreign Data Wrappers probably yea. — Jester, May 21 '14 at 21:21

score 9 · Accepted Answer · answered May 22 '14 at 12:56

9

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.

answered May 22 '14 at 12:56

Craig Ringer

307,061
76
688
778

1

SQL Server is somewhat both. Databases and schemas are pretty much the same thing as in Postgres - with the exception that you *can* do cross-database queries there without any additional setup. – May 22 '14 at 13:05
I really wish we could in PostgreSQL too. Doing it requires encapsulating a *lot* of currently-global data structures, though, and adding a whole new layer of context for queries. Among lots of other complexities. I don't think it's going to happen. – Craig Ringer May 22 '14 at 13:17
For example, the catalogs like `pg_class` are all accessed via syscache lookups, not direct relation reads. Lots of code *expects* to be able to find a data type, relation definition, etc, via a syscache lookup. The syscache would have to be taught to namespace entries by database oid, everything would need to be taught to pass `(dboid, reloid)` where it currently just passes the relation oid, etc. Messy. – Craig Ringer Jul 03 '15 at 07:47
i'm afraid that this answer has become odd due to time pass, as postgres have effectively added some functionallity to access to a database from another – Victor Mar 19 '19 at 14:28
@Victor Really? What? The options are the same as they always have been for ages - using `postgres_fdw` or `dblink` – Craig Ringer Mar 20 '19 at 06:43
1

@CraigRinger i have succesfully make a select from one database to another within the same postgres running instance using `postgres_fdw`, so actually i would say that cross-database queries are supported if you set up somethings properly. To anyone who lands here this answer proves to be very useful for me: https://stackoverflow.com/questions/29900748/how-to-connect-to-localhost-with-postgres-fdw – Victor Mar 20 '19 at 15:39

Cross Database Query in PostgreSQL

1 Answers1

Related