0

I have recently switched from MySQL to Postgres and am still is discovery mode.

In MySQL I could be connected to radius_db and still run a query against main_db.

However in Postgresql this doesn't seem possible.

radius_db=# SELECT COALESCE((SELECT UNIX_TIMESTAMP(expires_at) FROM main_db.device d WHERE d.id='123'), 0);
ERROR:  relation "main_db.device" does not exist
LINE 1: ... COALESCE((SELECT UNIX_TIMESTAMP(expires_at) FROM main_db.de...
                                                             ^
radius_db=#

Both radius_db and main_db belong to the same Schema (public). Is there a way to achieve this? Thank you

Houman
  • 64,245
  • 87
  • 278
  • 460
  • What MySQL calls a "database" is actually a schema. So you probably want to use schemas in Postgres in a single database –  May 27 '21 at 08:41
  • Thank you for your reply. This is an interesting idea. I could put each "database" into a separate schema. How do I access each in this case? `select * from schema2.main_db.device`? Sadly the question is already closed. (I don't want to use Foreign data wrapper) – Houman May 27 '21 at 08:51
  • `select * from schema2.device` - note that you have the hierarchy wrong (at least the way you wrote it in the question). A database contains multiple schema. So "main_db belongs to schema public" makes no sense. –  May 27 '21 at 08:55
  • Brilliant. Thank you. I'll give it a shot and may come back with a new question. :-) – Houman May 27 '21 at 08:57

0 Answers0