Cross Database Query in PostgreSQL [duplicate]

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
– IMSoP, Commented May 21, 2014 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
– Richard Huxton, Commented May 21, 2014 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
– IMSoP, Commented May 21, 2014 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
– Richard Huxton, Commented May 21, 2014 at 19:06

Craig Ringer · Accepted Answer · 2014-05-22 12:56:01Z

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, 2014 at 12:56

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user330315 Over a year ago

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.

Craig Ringer Over a year ago

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 Over a year ago

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.

Victor Over a year ago

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 Over a year ago

@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: stackoverflow.com/questions/29900748/…

|

Collectives™ on Stack Overflow

Cross Database Query in PostgreSQL [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related